Sep 19 2022 12:26 PM
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
Sep 19 2022 01:05 PM
See Create Dependent Drop Down Lists for instructions, examples and links.
Sep 19 2022 01:07 PM
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.
Oct 19 2023 11:22 PM
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?
Oct 19 2023 11:39 PM