I was having the same problem and came up with a workaround.
If you name the ranges of the lists you are using and then use those names when sourcing the lists for the cells.
Hope this helps.
I've designed a spreadsheet in excel 2010 that has a dropdown list and a macro attached to a button. These work fine in office 2010 but when another user opens the file using excel 2007, the macro encounters an error and the dropdown arrow has disappeared.
Any suggestions what I can do to resolve this?
I've tried changing the macro setting in excel but this didn't work.
Not sure if this is the correct forum but seeing as a macro was involved I thought I'd try here first!
If your dropdown is a data validation list and you have used a list on another sheet as the source, then you need to do what Tayt said, since versions pre-2010 cannot refer directly to a range on another sheet and the validation will simply disappear.
The issue occurs when the Excel 2010 document that contains list type data validation and the values for the lists reside on OTHER worksheets. This functionality didn't exist in Excel 2003 but it is in 2007. Nonetheless, when the functionality is used in this manner, it does not work when the document created in Excel 2010 is opened in Excel 2007. According to a Microsoft forum, this can be resolved by downloading the MS Office Compatibility pack at:
There are currently 5 users browsing this thread. (0 members and 5 guests)