Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am running into a bizarre error in Excel that I am hoping someone can help me with.

I have a macro-enabled workbook created in excel 2010. On the workbook are a series of radio buttons. When a radio button is selected, I use the selected value to populate a data validation list in another part of the spreadsheet. So, for example, I select the radio button for "Division 1" and that populates a list of regions specific to Division 1 and so on.

Each set of regions has a named range. The data validation list uses the INDIRECT formula to substitute a range name dependent on the selected radio button.

This works fine in Excel 2010. However, when I run the same file in Excel 2007, I do not get the dropdown list in the validation cell. I've followed all the troubleshooting steps outlined in the Contextures blog about disappearing dropdowns in validation lists but none of those have solved the problem.

Any ideas as to what might be going on?

share|improve this question
    
To narrow down the problem, have you tried manually creating the validations using the indirect function and see if that works in 2007? –  mtone Nov 2 '11 at 4:41

2 Answers 2

mtone - thanks for the response. Part of my issue was that I didn't realize there would be a difference in how Excel treated data validation in 2007 vs. 2010. I thought that the compatibility checks would have caught this. Apparently not.

The issue is that I was using an INDIRECT reference to a cell that was not on the same worksheet as the data validation dropdown. Apparently in Excel 2010 you can do this. In Excel 2007, this is not allowed and the data validation dropdown resets. Very annoying and frustrating to debug when you're not using Excel 2007.

share|improve this answer

Don't type the Indirect() function directly to the data validation box, circumvent using a named range that has the indirect function as a reference. That should fix the problem.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.