Populate a data validation drop down list based on a vlookup formula

Occasional Visitor

Hi all have a problem with data validation that no one (Yet) seems to know the answer to.

 

So, I have the following excel table with 2 columns (let's call it "table 1" column A & B):

 

Setup TOOL ID
Setup 1Tool ID 1
Setup 1Tool ID 2
Setup 1Tool ID 3
Setup 1Tool ID 4
Setup 1Tool ID 5
Setup 2Tool ID 6
Setup 2Tool ID 7
Setup 2Tool ID 8
Setup 3Tool ID 9
Setup 3Tool ID 10
Setup 3Tool ID 11
Setup 3Tool ID 12

 

I want to create a 2nd table ("table 2") with 2 drop down list (column A and B):

 

SetupTOOL ID
Setup 1 

 

so under "setup" in table 2, I want the user to be able to select from a drop-down list based on table 1 column A. I have done this with a simple drop list so no problem here. However, under "TOOL ID" in table 2, I want this to also be a drop-down list but that drop down list to only show the tool ID's associated with setup 1 by somehow looking up "setup 1" in table 1 column A and showing only the available Tool IDs in the second drop down box. So effectively this 2nd drop-down list should show Tool ID 1, Tool ID 2, Tool ID 3, Tool ID 4 and Tool ID 5. Anyone know how to get a second drop down list in table 2 under "TOOL ID" to produce a drop-down list based on a VLOOKUP of the available TOOL IDs for setup 1 from table 1?

 

Table 1 will grow over time, so each week there could be additional setups and tool IDs to choose from.

 

I have tried selecting "list" from the data validation options and typing a VLOOKUP formula into the "source" box so effectively trying to get excel to produce a dynamic drop-down list of column B from table 1 based on column A from table 2, but this doesn't work

Thanks Tom  

2 Replies

@Tom_Moore 

 

Here (attached) is an example I created of what I call "cascading data validation" where the secondary list is dependent on the first. You will need a current version of Excel, as this takes advantage of the relatively new FILTER function.