Data Validation - Significant Loss of Functionality - Error

[Gajendran Yadhav]

Dear folks,

I have been working on an Excel Workbook where I have 56 worksheets.

In all the odd number of sheets until (Sheet 48) (i.e., Sheet 1, 3, 5 and so on until 47) I have Data Validation in column O & P.
In column O - List data function is utilized and in column P, =indirect(o1) for dependent list function.

The data for this list & dependent list is stored in a separate worksheet - Sheet 56.

My Excel version is 2010. My colleagues use 2003 versions and few use Office 365. few use other spreadsheet software like Lotus or etc.

Hence I prefer to save all my files in 97-2003 type.

At the time of saving, I'm getting this error.

Thus I'm losing all my data validation and the file entirely seems to be of no use.

How to rectify this issue and save the data validation function?

Any help would be really appreciated.

Sample of worksheet: (Sheet 1,3,5,7 etc up to 47). {column O is List function & column P is dependent list}

The data for the List & Dependent List function: (sheet 55)

Thanks in Advance.

 

[RoryA]

In older versions you cannot directly refer to data on another sheet as a data validation source. What you can do is name the ranges on the other sheet, then use those names in the DV source box so instead of say =Sheet2!$A$1:$A$100 you would see =MyList