Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
It's just Microsoft. –  Lance Roberts Jun 23 '11 at 22:33
    
FYI: I installed SP1 today, and the issue persists. –  Madmanguruman Jul 5 '11 at 20:04
    
hey, man, solved? –  kokbira Jun 6 '12 at 13:49
    
@kokbira Nope! The issue persists. Of course, we are forced to disable validation but that's not 'fixing' the issue, it's avoiding it. The compatibility check engine is obviously broken. –  Madmanguruman Jun 6 '12 at 15:34
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Very good! It's interesting that the KB article took until September 2012 to show up, considering how long Excel 2010 has been on the market. Cheers! –  Madmanguruman Dec 1 '12 at 12:22
add comment

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.

share|improve this answer
    
I can't do that because LabVIEW only supports the binary .xls format, AFAIK. –  Madmanguruman Jun 24 '11 at 3:50
    
Also, the file was created in 2003 and has always been saved as 2003. From what I understand, the binary format is common from 97-2003. Regardless, doing simple, list-based data validation with the list on the same page as the validation rule should be no reason for Excel 2010 to choke. –  Madmanguruman Jul 5 '11 at 20:05
add comment

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!

share|improve this answer
    
This is an obvious workaround, granted. The main issue is that the flagged 'issues' aren't issues at all, and aren't the issues that are being described. –  Madmanguruman Mar 2 '12 at 21:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.