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

[Tom_Moore]

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 1	Tool ID 1
Setup 1	Tool ID 2
Setup 1	Tool ID 3
Setup 1	Tool ID 4
Setup 1	Tool ID 5
Setup 2	Tool ID 6
Setup 2	Tool ID 7
Setup 2	Tool ID 8
Setup 3	Tool ID 9
Setup 3	Tool ID 10
Setup 3	Tool ID 11
Setup 3	Tool ID 12

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

Setup	TOOL 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]

@Tom_Moore 

See Create Dependent Drop Down Lists for instructions, examples and links.

[#3]

@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.

[#4]

@mathetes 

Thank you very much for the useful formulas.

Nonetheless, my problem is a bit more complex as I need to input data in multiple rows of a table but formulas work only for the first row. Every time I try using the drop down list in the below rows, the option are stuck based on the first row. How can this issue be solved?

[#5]

the option are stuck based on the first row?
hide selected options in previous rows?
https://techcommunity.microsoft.com/t5/excel/options-in-drop-down-lists/m-p/3959811