Show Drop Down List With Specific Letters

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!)

datavalcontains03

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.

datavalcombosheet14

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.

datavalcontains04

If you leave the cell in column B empty, the full list of items appears in the drop down.

datavalcontains05

If no products have the string of letters, the drop down won't work.

datavalcontains06

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".

datavalcontains07

Or use the "?" wildcard, to represent a single character.

datavalcontains08

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.

You can visit my Contextures website, for more details on Advanced Filter basics, and how to create complex criteria for an Advanced Filter.

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.

_______________

One thought on “Show Drop Down List With Specific Letters”

  1. How can I jump to the alphabet where that range starts in excel? Ex I would like to click on J and go to the first name that starts with J.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.