Excel dependent drop-down list from regular table

0
votes

Please excuse me if this has been answered already in other posts but I looked forever for this and could not find my scenario. All the example I found involved static lists but I have a table that will have new entries added. I have a table with 4 columns as shown below.

enter image description here

I want to create two drop downs where the first drop down is for selecting the customer name, and the second drop down is for selecting one of the 3 addresses for that particular customer. I created an Excel table for all the data called "TBL_CUST" and then I defined a named range called "Customer" which is based on the first column of the table so the RefersTo value for the named range is "=TBL_CUST[Name]". I am using that for the data validation for the first drop-down and that's working fine.

So now what I need to do is create a second drop down that is dependent on the value selected on the first drop down and then offer just the values for the 3 other columns of that same record. This should not be difficult at all but I have struggled searching on how to do this with no success. How can I setup a second validation to accomplish this? I tried using a combination of indirect and vlookup but nothing worked.

A bonus would be clearing out the second drop down field when a value is selected in the first drop down :-)

2
excelvba

2 Answers

1
votes

Ok here's how I would go about it. Let's say your data is in a2:d8 and your first dropdown is in a12. I would create another column (which you can hide or put on a different sheet) which uses this formula starting in row 2. You can then make the second dropdown data validation reference this new column.

Formula for E2:

=INDEX($A$2:$D$8,MATCH($A$12,$A$2:$A$8,0),ROW())

enter image description here

0
votes

The template I created by adding dependent drop-down lists on the userform may be useful for you.

Visit enter link description here

enter image description here