Create list that progressively filters

[gdome]

I want to create 3 filters that progressively eliminates options as selections are made. The data set I have is:


State City Store
_______________________
State1 City1 Store1
State1 City1 Store2
State1 City2 Store3
State1 City3 Store4
State2 City4 Store5
State2 City4 Store6
State3 City5 Store7
State3 City5 Store8
State3 City5 Store9
State3 City6 Store10

Therefore, in the first drop, if I select "State2", I will get the following:

City1​

City2​

City3​

Then, if I select "City1", then I will get:

Store1​

Store2​

FYI, the purpose of this is to arrive at the 3rd selection, which is the store name. This will then be used as the input field to pull out a bunch of other data.

Any help with this will be greatly appreciated.


Thanks!

 

[pgc01]

Hi gdome

2 options

If you specify all the possible choices at each level:

http://www.contextures.com/xldataval02.html

I there are too many possibilities, you may prefer to use a table approach. In this case since you have 3 levels you need only to specify 3 tables.
The first table is like the one you posted, the 2 other are only with the first or the first and second columns, without duplicates, that you can generate automatically using the Advanced Filter.

I have an example here, check post #5:

http://www.mrexcel.com/forum/showthread.php?t=468615