How to create a dynamic (changing) drop down list based on a selection from another drop down list

Question

I've used this question to build my drop down list, but I want to know if this can be used to change one list based off of the selection on another.

Here's the question: Data validation - Drop down list with no duplicates in excel

In my spreadsheet, I have columns like so:

Clients    Stores

Client1    Store1
Client2    Store2
Client3    Store3
...        ...

Say in the Clients list, you select client1. I want to show only the stores that buy from client1 (say Store2 and Store6). This list will be updated constantly, and new clients and stores will be added to the lists. How would I go about doing this?

Thank you

Edit:

Here's the basic formula that I used to create my lists:

=IFERROR(INDEX(A$2:INDEX(A:A, MATCH("zzz",A:A )), MATCH(0, COUNTIF(Z$1:Z1, A$2:INDEX(A:A, MATCH("zzz",A:A ))&""), 0)), "")

Edit #2:

Here's a better representation of my data:

Order ID  Order Date   Client      Store
1234      MM/DD/YYYY   Client1     Store6
5678        ....       Client4     Store3
9101                   Client3     Store6
5432                   Client2     Store1

You're going to need a way to isolate your data points. If your data point is Stores which buy from ClientX, you need to specify how you are storing the Client>Store relationship. Do you have 2 columns where Client will have repeats such as: Client 1 | Store 1 Client 1 | Store 3 Or do you just have Column A Clients1-n Column B Stores 1-n and some other dataset which shows their relationships? You need the relationship data to answer this question. — shagans
I've discovered that I can kind of get what I want using INDEX MATCH, but it only returns one value... — andrewbuikema
Possible duplicate of Excel - how to create a dependent drop down list that uses different levels? — TotsieMae
Not a duplicate; I believe I am trying to do something different. — andrewbuikema

shagans shagans · Accepted Answer · 2017-12-13T20:23:11

Based on this sample data:

 A       B        C      D
order   date    client  store
1234            Client1 Store1
6543            Client2 Store1
5432            Client3 Store2
3214            Client1 Store3
9876            Client2 Store2
8765            Client4 Store5
7654            Client3 Store3

I created a Client List in Column F with formula: =IFERROR(INDEX($C$2:$C$8, MATCH(0,COUNTIF($F$1:F1, $C$2:$C$8), 0)),"") (paste into F2 and CTRL+Shift+Enter , Then drag formula down until Client names stop showing up). You'll want to adjust the columns and rows based on your data. So if your Client data goes from C2:C10000 use that instead of C2:C8. This resulted in:

  A       B        C      D     E     F
order   date    client  store       HideMe
1234            Client1 Store1      Client1
6543            Client2 Store1      Client2
5432            Client3 Store2      Client3
3214            Client1 Store3      Client4
9876            Client2 Store2      
8765            Client4 Store5      
7654            Client3 Store3

Then I highlighted the HideMe list and gave it a name. I recommend either using a dynamic range named list for easier maintenance down the road. Otherwise you'll need to come in and alter the named range every time a new client is added to your data. I named my range ClientList

I created a Data Validation Dropdown List with =ClientList which resulted in a dropdown which only has each client listed once.

I put that dropdown in H2.

I then put the following formula in column G to create a list of unique stores based upon the value of our dropdown in H2: =IFERROR(INDEX($D$2:$D$8, MATCH(0, IF($H$2=$C$2:$C$8, COUNTIF($G$1:$G1, $D$2:$D$17), ""), 0)),"") As with the other formula, CTRL+Shift+Enter and replace ranges to facilitate your data structure. Drag the formula down for a while. When you populate the dropdown in H2, the list will be dynamically updated so it only contains stores matched with the selected client. Create a dynamic named range or select enough of a range in Column G to accommodate your match options and name the range. I named mine ClientStores.

In Cell I2 I created my second dropdown list with Data Validation =ClientStores

Then you can hide your columns F and G so you are left with data and your two dropdowns.

Client Dropdown Example Stores Dropdown Example1 Stores Dropdown Example2

How to create a dynamic (changing) drop down list based on a selection from another drop down list

1 Answers