An Excel 2010 compatibility issue with 97-2003

[Boanerges_0800]

I have a spreadsheet that I created in Excel 2010 with some data validation (drop down list), a pivot table and some sumif/vlookup all in the same spreadsheet. I want to make it available to others (running Excel 2007) in our network using the same functionality (noted earlier) but I have been unsuccessful. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
When I tried to save the sheet as Excel 97-2003 Workbook (*.xls) there is a pop up box "Microsoft Excel Compatibility Checker" informing me that the data validation rules and pivot table style will not be saved. Therefore, I selected "continue" and Excel strips the functionality out of the sheet and saves it as .xls. <o></o>
<o></o>
So I opened the sheet again and went through the process of establishing the data validation again and pivot tables (in the Excel 97-2003 Workbook (*.xls)) version. When I tried to save it again I received the popup box again and the process repeated the same as paragraph two above.<o></o>
<o></o>
My questions are: <o></o>
<o></o>
1.)How do I create a sheet available for other users with different versions of Excel to be able to save their input and not have compatibility issues?<o></o>
<o></o>
2.) How do I update the sheet and save the changes when I run into the compatibility issues and excel strips the functionality?<o></o>
<o></o>
Thanks for reading!!!<o></o>

 

[Boanerges_0800]

The answer to my question for other users benefit:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Please observe this fact: Excel 2010 will not let you save in version 2007, it will only let you save to the oldest version which is titled Excel 97 - 2003 Workbook (*.xls). Not sure if this varies based on service pack but this is the reason I had to save in version 97-2003.xls below.

Basically, I had one employee list (approx. 300 names) that I wanted to use on multiple tabs throughout a spreadsheet, which you can do easily in Excel 2010. However, I have multiple users entering information into the tabs and they may be using versions of Excel Excel 97 - 2003.xls & 2007. So the only solution was to (1) Save the current sheet in Excel 97 - 2003.xls, note this will strip any 2010 functionality out through the compatibility checker (2) Copy the list to each tab and make a data validation using the list. (3) Redo the pivot table with Excel 97-2003 functionality. (4) I should note that I had to turn off the compatibility checker because it continually tried to check and report compatibility. Since I already saved and opened the sheet to check the compatibility worked by different users, I thought the checker was more of a nuisance for this particular sheet.<o></o>
<o></o>

Probably the only issue that I will have is if someone saves the sheet as an Excel 2010 *.xlsm again instead of an *.xls. <o></o>

 

[Jonmo1]

The problem here is that in xl2003, data validation can't refer to a different sheet.

The solution is to use a named range..

highlight your list
Insert - Name - Define
Name it MyList (or whatever).

Then in data validation (on any sheet) you can use
=MyList as the list source.

And this will be compatible in any version.

 

[Boanerges_0800]

Thanks, Jonmo1. That works in 2010 compatability mode. I tried it originally becuase someone suggested it on another site but I couldn't get it to work in the sheet I was using.

I just tried it in a blank sheet with made up data and it works. Not sure why it didn't work in the first place but I wish it did.