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.
