Excel Dependent and Searchable Dropdown List

Occasional Visitor

I am trying to create a Searchable AND Dependent dropdown list in Excel.

 

Currently I have tables that include State Abbreviations and Cities in those States. For example, in cell A2, there is a list of states including, Iowa, Minnesota, Illinois, etc. In cell B2, there is a table with all cities in Iowa. In Cell C2, there is a table with all cities in Minnesota.

 

I figured out how to create a searchable list, by using the following formula:

=SORT(FILTER(State,ISNUMBER(SEARCH('Sheet1'!C3,State)), "Not Found"))

 

Then I use the following formula within Data Validation (turning off errors) in cell C3:

='Sheet 2'!$D$2#

 

So in cell C3, I can start typing in Minn, click the dropdown and it lists Minnesota. My issue comes in when I try to select the cities in Minnesota. I am using the INDIRECT formula now, but it does not allow me to search.

 

I am open to using VBA, but cannot find anything on it at the moment.

1 Reply
Here's a tutorial about searchable dropdown lists:
https://youtu.be/Ea_ACp5W8zI