Symptoms
If you have a Data Validation list in a Worksheet that is not active when the Compatibility Checker runs, it will fail. The Compatibility Checker produces an error as follows;
Excel 97-2003
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.
Cause
Microsoft has confirmed that this is an issue in the Microsoft products that are listed in the "Applies to" section.
Resolution
The warning is incorrect, there is no loss of functionality. You can save the file without losing the Data Validation. You can also avoid this message by one of the following workarounds:
1. Save the file in an XML format such as XLSX, XLSM or XLSB.
2. Uncheck the "Check compatibility when saving this workbook" option.