Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I have two cells and a named range "Mexico":

A1 contains the string: "Mexico"
A2 is the cell that has required data validation (drop down list).

I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "Mexico" to a named range Mexico.

I tried inputting =INDIRECT(A1) into the cell's validation window.

share|improve this question

migrated from Nov 8 '12 at 11:28

This question came from our site for professional and enthusiast programmers.

You have quite a few questions that haven't been closed. Have you really not gotten a good answer out of any? If you did, try accepting the correct answers, this will help you get answers in the long run. – nutsch Nov 7 '12 at 18:36
Appears to be a duplicate of INDEX(INDIRECT(“DefinedName”),1)=#REF while INDEX(DefinedName,1) works – brettdj Nov 8 '12 at 7:59
I have accepted them. In the sense that I have said "yes" to "was this question useful to you". Maybe I am missing something in closing a question? – user1717622 Nov 8 '12 at 11:16
Are you not able to click on an outlined tick next to each answer to cause the tick next to the answer that suits you to turn green? – pnuts Nov 8 '12 at 13:35

You cannot use the indirect data validation model with a dynamic named range.

Check out this reference to get more info on the subject

share|improve this answer

in your named range is an error therefore it will not give an actual range. The sheet name in the indirect should be added to the address like this:

"'list range'!"&ADDRESS(...)

WIth that in mind you can also just have a single named range that will always turn up with your desired range for the drop down list.

Give it a fixed name (for example rangeCountry) and use that for the data validation. (fixed name, not indirect, that wont work!)

share|improve this answer

Your Answer


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