Reply to thread

Message: [QUOTE="BoringAccountant, post: 14013200"] 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. [/QUOTE]

Verification