Excel Dependent and Searchable Dropdown List

%3CLINGO-SUB%20id%3D%22lingo-sub-2333014%22%20slang%3D%22en-US%22%3EExcel%20Dependent%20and%20Searchable%20Dropdown%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2333014%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20Searchable%20AND%20Dependent%20dropdown%20list%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20have%20tables%20that%20include%20State%20Abbreviations%20and%20Cities%20in%20those%20States.%20For%20example%2C%20in%20cell%20A2%2C%20there%20is%20a%20list%20of%20states%20including%2C%20Iowa%2C%20Minnesota%2C%20Illinois%2C%20etc.%20In%20cell%20B2%2C%20there%20is%20a%20table%20with%20all%20cities%20in%20Iowa.%20In%20Cell%20C2%2C%20there%20is%20a%20table%20with%20all%20cities%20in%20Minnesota.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figured%20out%20how%20to%20create%20a%20searchable%20list%2C%20by%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3DSORT(FILTER(State%2CISNUMBER(SEARCH('Sheet1'!C3%2CState))%2C%20%22Not%20Found%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20use%20the%20following%20formula%20within%20Data%20Validation%20(turning%20off%20errors)%20in%20cell%20C3%3A%3C%2FP%3E%3CP%3E%3D'Sheet%202'!%24D%242%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20cell%20C3%2C%20I%20can%20start%20typing%20in%20Minn%2C%20click%20the%20dropdown%20and%20it%20lists%20Minnesota.%20My%20issue%20comes%20in%20when%20I%20try%20to%20select%20the%20cities%20in%20Minnesota.%20I%20am%20using%20the%20INDIRECT%20formula%20now%2C%20but%20it%20does%20not%20allow%20me%20to%20search.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20open%20to%20using%20VBA%2C%20but%20cannot%20find%20anything%20on%20it%20at%20the%20moment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2333014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2342351%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Dependent%20and%20Searchable%20Dropdown%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342351%22%20slang%3D%22en-US%22%3EHere's%20a%20tutorial%20about%20searchable%20dropdown%20lists%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FEa_ACp5W8zI%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FEa_ACp5W8zI%3C%2FA%3E%3C%2FLINGO-BODY%3E
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