Excel 2010 data validation warning (compatibility mode)

Question

We have some legacy worksheets that were created in Excel 2003, which are used by LabVIEW-based test automation software. The current LabVIEW software can only handle the legacy .xls format, so we're forced to keep these worksheets as-is for the time being.

We've migrated to Office 2010 and when working with these worksheets, I see this warning:

"The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in the currently selected file format. Click Continue to save the workbook anyway. To keep all of your features, click Cancel and then save the file in one of the new file formats."

"Significant loss of functionality"

"One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved."

When I click 'Find', some cells that do indeed have validation rules are highlighted, but those rules are all on the same worksheet!  We're using simple list-based validation, with some cells off to the side containing the valid values (for example, cell B4 has a List with Source "=$D$4:$E$4")

This makes no sense to me whatsoever. One, the workbook was created in Excel 2003, so obviously we couldn't implement a feature that doesn't exist. Secondly, the modifications we're making don't involve changing the validation rules at all. Thirdly, the complaint that Excel is making is incorrect! All of the rules are on the same worksheet as the target.

Any recommendations, other than "upgrade LabVIEW' or "reinstall Excel 2003"?

Adam

Answer 1

In Excel 2010 do not drag the border on the list to expand the selection, process as if it is 2007 Excel. Drag highlight the cells for the list and enter a name in the name box and click enter. Go to the sheet and click on cell you want list in -- then click data validation -- then enter the name on the source line. Saving in 2003 there is no error.

Thanks. my problem resolved! :)

Answer 2

In Excel 2010 do not drag the border on the list to expand the selection, process as if it is 2007 Excel. Drag highlight the cells for the list and enter a name in the name box and click enter. Go to the sheet and click on cell you want list in -- then click data validation -- then enter the name on the source line. Saving in 2003 there is no error.

Answer 3

Are you referring to this text?

"One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be supported in earlier versions of Excel.

What it means    In Excel 2010, you can implement data validation rules that refer to values on other worksheets. This is not supported in Excel 97-2003 and Excel 2007.

What to do    In the Compatibility Checker, click Find to locate the cells that contain data validation rules that refer to values on other worksheets, and then make the necessary changes so that the data validation rules refer to values on the same worksheet."

Remember, the worksheet was created in Excel 2003.

When I click Find to see the supposed data validation violations, I'm shown values on the same worksheet.

Answer 4

Check the unsupported formula features section and look for data validation

http://office.microsoft.com/en-us/excel-help/use-office-excel-2010-with-earlier-versions-of-excel-HA010342994.aspx

Answer 5

As if the story wasn't bizarre enough:

I went ahead and saved the worksheet with Excel 2010. I then went to an old computer back in the lab and opened the document with Excel 2003. Guess what - the validations were untouched!

I'm *really* scratching my head now...

Adam