Using the INDIRECT() function with table formats in Excel 2007 [closed]

Question

My purpose is to create data validation dropdown lists in Excel that reference whole columns in a table-formatted set of data. Ultimately, I hope to create a dynamically changing set of data by which the dropdown will be defined.

In other words, I would like to know if it is possible and good practice to create a dropdown list based upon table references instead of static named ranges?

Example:

Assuming a named range "PART_LIST" that is a 2x1 array with the following cell entries:

PART_1  
PART_2

I also have 2 Excel Tables named "PART_1" and "PART_2" with 2 columns:

column1  
column2

Now I have a data validation list (in cell A1 of a separate worksheet) with Source:

=PART_LIST

Here's the tricky part...
I want to create a secondary dropdown that uses the selection from the first dropdown, which corresponds to one of the 2 named tables. This is pseudo-code that will not work in Excel, but effectively here is what I would like to add as my Source for this second dropdown:

=INDIRECT($A$1[[#Data],[column2]])

So basically I would like to use the user's entry in the first dropdown as an INDIRECT() reference to "column2" of my second dropdown.

I may be making this much more difficult than it needs to be. If so, please feel free to correct my practice here.

Answer 1

You might be thinking about the problem backwards. Since you already have somewhere you're creating lists to feed into a data validation dropdown, it would probably be easiest to create one more list to be the source of your second. This list would be =if($A$1="Part_1",Part_1,Part_2) and would change according to what was selected in the first drop-down box.