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.


  • 1
    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

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

Check out this reference to get more info on the subject


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!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy