Use drop-down data validation list pointing to a cell range on another sheet whose name comes from a cell on this sheet

[AChimpNamedCornelius]

I have a sheet where I want to use a data validation drop down list. However, I want the sheet the list comes from to be pointed to by a name in a different cell on the current sheet.

So say sheet A has the drop down list source. Sheet B has the column that will use it. But I want a cell on sheet B to contain "A" so the data validation knows to pull it from sheet A. I might want to put a "C" there to pull from a different list on sheet C.


Again, this is easy enough if I use (globally) named ranges. The problem is I must enter that custom name in the Data Validation setup tool. That hides it, which I do not want. I want the user to be able to enter a tab name in a cell, and that dropdown comes from that tab, whatever it is.


So these are allowed Data Validation (List-type) references in that dialog:

=Merged!$A$2:$A$9 (where Merged is the other tab)
or
=ListRegionInMergedTab

where ListRegionInMergedTab is the same region done as a (globally) named region.




Something like this is what I want, but it does not work (goes into Data Validation editor, not a cell remember):

=indirect("B1")!$A$2:$A$9

Where B1 is a cell on this sheet that has "Merged" in it. An alternative idea I cannot seem to make work either: User enters a (globally) named range in the cell, and that gets used.

 

[AChimpNamedCornelius]

Solved it, sigh. This construct is clumsy but works.

On "this" sheet, say C1 holds ListRegionInMergedTab as a string. I can use this:

=indirect(indirect("C1"))

The first Indirect gets the contents of C1, which is ListRegionInMergedTab, and the second turns it into a legitimate named range reference. The dropdown fills with the list from the other sheet.

Is there a way to do this with a tab name reference though? In other words, say cell D1 had "Merged", the sheet's tab name. What would be a reference acceptable to the Data Validation editor that pointed to cells A1-A100, using D1's contents as a tab name?

 

[AChimpNamedCornelius]

Solved it using just the tab's name as a reference.

=indirect(concatenate(indirect("B1"), "!$A$2:$A$9"))

Where B1 contains "Merged" (without the quotes). This yields a drop-down list on the other worksheet pulled from the given range from a tab whose name is in B1.

 

[AChimpNamedCornelius]

I note if the name of the tab contains a space or ( or ) then it doesn't work.