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. To see the code, right-click on the DataEntry sheet tab then click View Code.

_______________

You may also like...

9 Responses

  1. peter says:

    Hi,

    DV0066 – Data Validation – Contains Text

    can’t see macro module??
    If hidden, how?

    peter

  2. Chris says:

    Hi I can’t see the macros eiher

  3. Louis says:

    Agree! Details on the Macro will help to develop a new one following the path.
    Can it be used as a template to build a new one?

  4. Martin Canosa says:

    Hi Debra !!

    what about using wildards in a combobox? I’m using a dropdown with 3 categories: id, name, and SSN. the selections indicates the field to look from to a second dropdown, populated with the result of the first selector.

    If I select, for instance, Name, then I’d like to type in SMITH, and that the result being smith, smithson, graysmith. just the ones containing the string, which in SQL would look like Select Name from Table1 where Name like ‘*smith*’.

    any ideas?

    Thanks !!

    Martín

  5. Luis says:

    thanks for sharing your knowldege!! Would you mind helping me operate your macro datavalidationcontains.xlsm?
    when i type the letters in col B and press the drop down arrow, I get the standard X stop error message “value you entered is not valid. A user has restricted etc., retry cancel and so on.

    Any idea what’s preventing it from running normally?

    Thanks

    Luis

  6. Luis says:

    Hi again,

    Please disregard my earlier post – just discovered what i was doing wrong duh :)

Leave a Reply to peter Cancel reply

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