While featuring in Money Man's 24 track, Lil Baby explains his challenge of deciding which car to drive, as he has many options:
"Which one I'ma drive? I got sixteen options"
Like Lil Baby, you may have faced a challenge with your Dropdowns in Excel where there are just too many options:
In the Example above, we have over 100 different options to choose from, and not having them in any specific order is no help.
So how about creating a Search Box for the dropdown, that limits what options are available?
First, we'll dedicate a cell that the user can use to search.
Next, we'll use a blank cell, to do the calculation:
The formula should be as follows:
=FILTER(DataValidationRange,IFERROR(SEARCH(SearchTerm,DataValidationRange),0)>0)So for my case, I am getting the SearchTerm from the G1 cell:
=FILTER(DataValidationRange,IFERROR(SEARCH(G1,DataValidationRange),0)>0)I'm using a Table to source data for my dropdown, so my Data Validation Range is as follows:
=FILTER(Words[Interesting Words],IFERROR(SEARCH(G1,Words[Interesting Words]),0)>0)Now, our formula is complete. The formula returns all relevant options for whatever is in the Search Box:
Now, we'll simply click where we want our dropdown, and point to the cell containing our formula followed by a # symbol.
And that's it! Now you have a Searchable Dropdown! Perhaps now you can find your lost love!
Comments