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.

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!

My questions are: is this a legitimate bug in Excel 2010, or is this some exotic error in the legacy .xls worksheet that is confusing the heck out of Excel 2010? Has anyone else observed this issue working in compatibility mode?

Answer 1

This is a known error in Excel 2010 compatibility checker.

It happens when you try to save the sheet in xls format and you have data validation formulas on a sheet that is not the active sheet when you perform the save.

The error is incorrect and there is no loss of functionality. Refer to Microsoft knowledge base article 2757267.

To verify this, start a new workbook in 2010. On sheet1, enter a data validation list in a single cell referring to a few adjacent cells. (thus it does not break the rules of referring to cells in another sheet).

Do save as and select xls as file format. No error message is produced. Move to sheet 2. Do save as and select xls as the file format. Error message is produced with nothing in the worksheet but a single data validation list.

Answer 2

Sounds like the error you get when you try to save advanced features in an old format - like excel 97. Perhaps this file was originally created in 2003, but saved as 97 compatible or something like that. Saving as a new 2010 format should not be a problem, and that is in fact what the message is saying.

Answer 3

I think a solution to your problem could be the following:

• Open the Excel file in question with Excel 2010

• Click the "File" tab, and then click on "Info"

• Scroll down to the button "Check for Issues" and click on it. Then click on the option "Check Compatibility". A little window opens and you can untick the checkbox: "Check compatibility when saving this workbook."

• Click OK

• Save the file.

• The next time you save the file you should see no error message.

But please notice: this fix prohibits Excel from warning you if there ARE some other issues with your Excel file. So you must be absolutely sure that there really are NO other issues!