MS Excel has some known inconsistency or incompatibility when it comes to its data validation list feature. The exact problem is that, if your file contains data validation lists created in Excel 2007, these data validation lists are gone when you open it in Excel 2010.
If this “data validation” thing in Excel is new to you or something that you have not tried so far, please read this article for the complete and detailed procedures in creating and using data validation lists: http://www.technicalforum.org/ms-office/excel-200720102013-using-data-va…
Let us take as an example the sample file I shared in my previous article. It can be downloaded from the link or button below:
Now, the data validation list settings that produce the combo box in the screen shot above is this:
Unfortunately, this file when opened in other versions of Excel will lose all the data validation lists settings including the combo boxes when you select the cells with data validation like cell B7.
How to Make Data Validation List That is Compatible to All Excel Versions
In order to make your data validation lists compatible across all versions of MS Excel, there is only one thing you need to do in creating the valication list. That is to “name” the cell ranges that you will be using as the “source” cell range like the one shown in the screen shot. That means that instead of using =DATA!$D$2:$D$6, use “named cell ranges” instead like =education.
How to Name Cell Ranges
To name cell ranges, here are the steps:
- Simply highlight the entire range, excluding the header or the title of the column.
- Then in the name box that is located right above column A, click on it then enter the name of the selected cell range.
- Then press Enter. The selected range shall be named this time.
- To test if you named your cell ranges correctly, simply select the range and the name of the range shall be displayed in the Name Box.
Now, in order to create a cross-version data validation list, you need to enter the “name” of the cell range for the SOURCE instead of the cell range notation itself. This simply means that you need to redo the setting for your data validation list if you are working on a file with an incompatible data validation lists. Do not worry, when you select the range for the SOURCE, the assigned names will be provided automatically.
After completing the entire process for all your data validation lists, the resulting Excel file will be compatible with other versions of Excel.
You can download the sample file in the link below.