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?
Assuming a named range "PART_LIST" that is a 2x1 array with the following cell entries:
I also have 2 Excel Tables named "PART_1" and "PART_2" with 2 columns:
Now I have a data validation list (in cell A1 of a separate worksheet) with Source:
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:
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.