Data validation set in Excel 2010 fails in Excel 2007

1
votes

I have created an Excel 2010 document that uses a drop down list in one column via the data validation feature.

The validation field is set to 'List' and defines a range on another datasheet:

=Sheet1!$A$1:$A$7

When that document is opened in Excel 2007, those settings are lost and the drop down does not appear.

Why is that?

1
excelms-office
how is your list defined? does it refer to a named range? – JMax
Actually, you couldn't refer to another Sheet with data validation in Excel version prior to 2010. You can answer your own question and accept it so that further user will find it solved. – JMax
Actually you can refer to another sheet with validation in Excel 2007. You can't select another sheet if you choose validation from a list, but you can type in the range directly like =sheet2!a1:a10. But if you then save in 2007, re-open it in 2010 and save it in 2010, you can't access the validation from Excel 2007. The solution whether starting from 2007 or 2010 is to proceed as in the answer below. – Tom Sharpe

1 Answers

2
votes

The answer is that the range has to be named, and referenced like 

=MyRangeName