drop down list compatibility issue between Excel 2007 and Excel 2010

Question

I prepared a spreadsheet where I used the drop down list command in several cells (data validation, list). I needed to provide two versions of the same spreadsheet: one saved as Excel 2007 file, and the other one as Excel 2010 file.

If I open the Excel 2007 file with Excel 2010, in the cells where I inserted the drop down list, the reference to the cells that contain the list is somehow changed and the drop down list shows the wrong data.

For example, if I specify to use as the data in the list the values of cells C20:C25, when I open the file with Excel 2010 the cells are changed to D15:D20. I did not find a trend in the "shifting" of the referenced cells.

How do I prevent this without using Excel 2007 for the 2007 file only and Excel 2010 for the 2010 file only?

Answer 1

I have found a workaround that works for me.

If this is your original dropdown box formula: =Sheet2!A1:A5

Then change it to: =INDIRECT("Sheet2!A1:A5")

For me, this stops it removing the dropdown when opening in Excel 2007 after saving in Excel 2010. I found this out accidentally because I needed INDIRECT to define my dropdown range, as it varied.

Jeremy

Answer 2

I found the answer here: http://www.ozgrid.com/forum/showthread.php?t=150080

"If your dropdown is a data validation list and you have used a list on another sheet as the source, then you need to do what Tayt said, since versions pre-2010 cannot refer directly to a range on another sheet and the validation will simply disappear."

That solved my problem; hopefully it'll solve yours.