a data validation list that contains a list of named ranges - not alist from a named range

[BoringAccountant]

Hi, I searched a bit for an answer to this question but could only
find information on a basic data validation list that is bound to a
named range. That part, I understand, but I'm having trouble figuring
out if this is possible:

I have a function in Excel that takes an input of a single cell (A1),
a range of cells (A1:A6), or a named range of cells (Regions).

I have defined several named ranges in my worksheet. For example I
have two named ranges with text values in each cell of the range:

Named Range 1: "NorthwestRegion"
Cell 1 Value: "Seattle"
Cell 2 Value: "Vancouver"
Cell 3 Value: "Las Vegas"

Named Range 2: "Southwest Region"
Cell 1 Value: "San Francisco"
Cell 2 Value: "Monterrey"
Cell 3 Value: "Tempe"


Using the named ranges in the formula is no problem, I can use the
insert function wizard and type my named range into the formula's
parameter and it recognizes the range names and displays the resolved
values to the right of the parameter.

The problem occurs when I try to take this concept a step further.

I have an additional named range with my region names:

Named Range 3: "Regions"
Cell 1 Value: "Northwest Region"
Cell 2 Value: "Southwest Region"

In an attempt to make is easier to switch between named ranges in the
formula, I have anchored formula to the cell containing a Data
Validation List of the additional "Regions" named range. I can see my
list of "Parent" regions in the Data Validation List, however when I
select the different region names "Northwest Region" or "Southwest
Regions", those values are treated as text and not actual range names
to be evaluated and passed to the formula for calculation.

I was thinking there may be some syntax to let Excel know that it's a
range like
"Range!NorthwestRegion" that I could add in front of the values in the
named range. Anyone know if that's the case or is there another way to
do this?

I have been to use a VLOOKUP in a roundabout way to accomplish this,
but it's a very problematic approach and very fragile.

For example,

I have the names of the regions and thier values on each row ("Pipe" |
denotes new cell)

NorthwestRegion|Seattle|Vancouver|Las Vegas
Southwest Region|San Francisco|Monterrey|Tempe

I then have a row just below these that contains a data validation
list of these "Parent" range names, just like the 3rd range in my
first example

Named Range "Regions"
Cell 1 Value: "Northwest Region"
Cell 2 Value: "Southwest Region"

Whenever I select the "parent" region from the drop-down list my
VLOOKUP returns the 3 cities into three cells to the right of the data
validation list

so it ends up looking just like my first or second row depending on
the "Parent" region selection

(Drop-down cell) |VLOOKUP
NorthwestRegion|Seattle|Vancouver|Las Vegas

I then reference these three cells containing the cities in my formula
and can use the drop-down list switch between the regions and the
values are passed into my formula with no issues.

My problem in this example:

1. Regions may not always be comprised of 3 cities.
2. Expanding my VLOOKUP to return more rows to cover the expected grow
and shrink of the cities does not work because the blank rows are
passed to my formula which can interfere with the results.

I hope that makes sense.

 

[Max]

Think you could try INDIRECT, something like this:
=VLOOKUP(C2,INDIRECT(D1),2,0)
where D1 houses the DV which returns the named range