Indirect not working with array formula?

Hi everyone,

Appreciate if anyone could help me with this. I'm stuck at this for days now.
My final goal is to get the INDIRECT formula working so I can setup DATA VALIDATION (list) with it. I want to have a dependent drop down list which leaves out blanks and sort the items in drop down list alphabetically.

I am trying to get INDIRECT to work with my formula.

Let's say in cell A1, I have a set of formula which results in the following text:

LocationTable!B$3:INDEX(LocationTable!B$3:B$11,COUNTIF(LocationTable!$B$3:$B$11,"?*"))

Notice that the range above is dynamic depending on what input I key into my first drop down list.

E.g.
It could be:
LocationTable!B$3:INDEX(LocationTable!B$3:B$11,COUNTIF(LocationTable!$B$3:$B$11,"?*"
LocationTable!C$3:INDEX(LocationTable!C$3:C$11,COUNTIF(LocationTable!$C$3:$C$11,"?*"
LocationTable!D$3:INDEX(LocationTable!D$3:D$11,COUNTIF(LocationTable!$D$3:$D$11,"?*"
LocationTable!E$3:INDEX(LocationTable!E$3:E$11,COUNTIF(LocationTable!$E$3:$E$11,"?*"
and so on (depending on what is in the first drop down)

Now, in other cell, I put in the following:

=INDIRECT(A1)

This is the code that I would ultimately use to put in my second (dependent) drop down.

Is there other way to make it work? Thank you in advance

Originally Posted by Norie

Have you tried it in a drop down?

Yep. I tried both using cell and drop down. Drop down gives me error and blank list, while cell gives if #REF! error.

The file I am currently working on:
Example - Copy2.zip

Please ignore the mess.

Originally Posted by Norie

I'm sorry but I can't see what the problem is.

When I try and evaluate the formulas I end up with REF# errors.

Have you tried using Named ranges?

That's what's usually used with dependent data validation

Not yet. Probably it would work, but due to the different possible entries, there could be MANY items in the first drop down list, which means that I will need to make hundreds of named range for it to work. :/

Oh, could anyone change the title of the post for me? It should be "Indirect not working with dynamic range?" instead.