Table multiple filters with drop downs

[Costas]

Hi Chandoo,

I've got a tricky one to ask. I've got an excel table with a list of countries, their states and territories and major cities.

I want to create a drop-down of the countries and another drop-down of states and territories that filters to the country selected in the first drop-down.

Below these two drop-downs, I want to have a list of the cities that satisfy the two criteria.



I'm thinking of writing a code on change but wanted to ask if there is another alternative with advanced filter perhaps or something else. I attach my file for more info.

Thanks
Costas

 

[Chihiro]

There are multiple ways to do this. Actual solution will depend on your Excel version, use case and user's Excel version (backward compatibility requirement).

With your current Output sheet set up. Advanced Filter can't be used. Criteria column header and data headers all must be exact match to headers in "Data" sheet.

Have a read of...
https://www.contextures.com/xladvfilter01.html

If using Office 365.
=FILTER(Table1[City],(Table1[Country]=Output!B1)*(Table1[State/Territory]=Output!E1))

If you need backward compatibility...
=INDEX(Table1[City],AGGREGATE(15,3,(ROW(Table1[City])-1)/((Table1[Country]=$B$1)*(Table1[State/Territory]=$E$1)),ROWS($A$1:A1)))

Copy down as much as you need. If you don't want to see error values. Nest it in IFERROR(Formula,"")

 

[Costas]

Hi Chihiro,

Many thanks for your reply.

I'm using Office 365 but couldn't get the filter function to work but the second option works fine.
My only problem now is that the drop down of Output E1 still shows all states instead of just the ones for the country selected in Output B1. I attach the revised file.

Thanks
Costas

 

[Chihiro]

In your filter function, you missed inner brackets.
=FILTER(Table1[City],(Table1[Country]=N_Country)*(Table1[State]=N_State))

For state dropdown list, have a read of link(s) below.

https://www.contextures.com/xlDataVal02.html
https://www.contextures.com/xlDataVal13.html

In link below, you'll see dynamic dependent dropdown set up sample file I did in another forum.
https://www.excelforum.com/excel-fo...7-dynamic-dependent-data-validation-list.html

 

[Costas]

Hi Chihiro,

That's exactly what I was looking for.

Many thanks for your help!!
Costas