If you’ve got a long list of items, it can take a while to find what you’re looking for, in a data validation drop down list.
For example, in the screen shot below, you’d have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don’t pick the Tofu by mistake!)
Type the First Letters
I’ve previously posted a few variations on a combo box technique, that lets you find an item based on the first few letter that you type.
But that trick won’t help if you don’t know the exact name. Maybe you know that there are chocolate biscuits in the list, but not the brand name.
Type a Few Letters
To help you out in that situation, I’ve uploaded a new sample file. In this workbook, there is a data validation drop down in column C, with a list of product names. If you type one or more letters in column B, the drop down list in that row will only show the items that contain that string of letters.
If you leave the cell in column B empty, the full list of items appears in the drop down.
If no products have the string of letters, the drop down won’t work.
Use Wildcards in the Text String
To give the searches a bit more flexibility, you can use wildcards in the text strings, in column B. For example, type “a*x”, to find any product that contain the letter “a”, then any number of characters, followed by the letter “x”.
Or use the “?” wildcard, to represent a single character.
Advanced Filter Programming
To create the short list, an Advanced Filter extracts products from the main list, based on the criteria that you typed in column B. The code runs automatically, when you click on a cell with a drop down list.
Download the Sample File
To see how this technique works, you can download the sample file from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0066 – Data Validation – Contains Text
The zipped file is in xlsm format, and contains macros. Enable the macros, when you open the file, if you want to test the drop downs and search strings.