Shorten Data Validation List With Excel Filter Macro

Shorten Data Validation List With Excel Filter Macro

An Excel data validation drop down list only shows 8 items at a time, and with a long list of items, it might take you a while to scroll through that long list.

To make data entry easier, see how to shorten data validation list, by using a macro.

Long List of Names

For example, this drop down list is in cell C3. When cell B3 is blank, a long list of names shows in the drop down list. You can scroll up or down, to see the other names.

DataValFilter01

Shorten the List

Instead of showing all the names in the drop down list, you can type a letter (or letters) in cell B3.

That automatically runs a macro, to show a short list of names in the drop down, based on the starting letter(s) that you typed in cell B3.

show a short list of names in the drop down
show a short list of names in the drop down

Filter the Source List

To create the short list of names, the macro in this example runs an advanced filter, using the typed letter(s) as the filter criteria.

The matching names are filtered into a dynamic named range, which is the source for the data validation drop down.

The range is named NameList, and uses the following formula:

=OFFSET(ExtractNames,1,0,COUNTA(‘Sales Data’!$J:$J)-1,1)

DataValFilter03

Filter the Orders List

In the sample file, there are orders listed on a Sales Data worksheet. In addition to creating a short list for the data validation drop down, the macro also filters the list of orders.

When you type one or more letters in cell B3 on the Summary sheet, then press Enter, all the sales orders with a name that starts with that letter sequence are filtered onto the Summary sheet.

DataValFilter04

To see a shorter list of orders, select a name from the drop down list in cell C3. The orders with that name are filtered onto the Summary sheet.

DataValFilter05

Download the Sample File

To see the Excel macro that filters the orders and the data validation source, you can download the sample file from my Contextures website.

Go to the sample file page, and in the Filters section, look for FL0024 – Shorten Data Validation List

The file is in Excel 2003 file format, and you’ll have to enable the macros to test the file.
_______________

0 thoughts on “Shorten Data Validation List With Excel Filter Macro”

  1. Another option would be to use a combo box control (the Visual Basic version),which automatically offers you “type ahead” choices based on what you’ve typed so far.

    Then you wouldn’t need to select a letter of the alphabet to start with.

  2. hi Debra,

    I was navigating your blog trying to find a solution to a problem that I have now with data validation.
    I have a price list, with product description an unit price, which I’ve set up as you taught, hiding the used items from the validation list.

    My problem is that I want to use something similar to the find-as-you-type match,like the combo box, so in the Order form i have to set up one combo per line, and linked it to the validation list. I’ve found that universal temp combo on your blog, which i think is the solution, but the list is not being displayed correctly.

    any ideas on how to fix it?

    Thanks,

    Martin

  3. Hi,

    Can you give me the idea of while doing validation how can i select list like in filter(pressing alphabet). example: suppose Prakash is the area of validaion and i need to see prakash in the cell pressing P.

    Thanks,
    Praksh

  4. Martin,

    i have given some data below, Could you please give me the sumif formula for it. Please send it to me my email attaching on excel file.

    Brand Division DV code Qty
    Gap Men F 10
    BR Women D 30
    Gap Kids E 40
    Gap Baby G 50
    Gap Access K 60
    Gap Men L 20
    Gap Baby O 100
    Gap Kids F 60
    Gap Access D 80
    Gap Men E 60
    BR Kids G 20
    Gap Access K 30

    Need Formula Below in Qty column.
    Summary
    Brand Division DV code QTY
    Gap Men Women Kids Baby F E G O “IF brand =gap,
    division =men,women,kids,baby,
    dvcoce=f,e,g,o
    need Qty sum here”

  5. You are one of the few people I have found online that talks about the issue I am trying to solve. At the same time, I don’t quite follow how to create this Shortening Data Validation List. I cannot find the macro you mention. Can you help me create this list? I can send you my file…or maybe you can direct me to a YouTube video that shows me what to do? Either way, I would be gfrateful for your help.
    THE EXACT ISSUE: My issue is that I have a list of 300 – 6000 items, and it is time consuming to scroll through the list to find the right item. It would be better if they could type a letter or two in the cell, and the list would appear and shorten automatically as they type.

      1. I tried using the files at the link you rpovided. It does not seem to work (probably user error). Nevertheless, I am not sure that the solution you are talking about is the same things I am trying to get my spreadsheet to do. I will email you some screenshots of what I am trying to get my xls to do?
        Thanks,
        Erik

  6. I have a master list, and a transaction list. I am using list to select Machine Serial No from master sheet. But as the master sheet has 200 line items it becomes difficult to use list as I have to scroll done the entire list to select the machine no I am looking. How can I filter to shorten the list on specific 2 to 4 caracter in machine serial number from master list.

  7. Hello,
    Any suggestions on how one can change the use of Cell B3 on any letters/words (full or partial) rather than the first sequenced letters found in the database (column C in the Sales Data tab)? I’d like to make my file more robust and have the capability of me typing in any letter and retrieve the matched row.
    Assistance would be greatly appreciated.

  8. Hi Debra,
    I’m really find this very useful to me, thanks for share it. but i have one question, what if i made not only B3 & C3 in summary sheet that can be type and search, but also let say B4, B5, B6, and C4 until C6, that can also do the filter with one database? any idea about how’s it done?

Leave a Reply to Ananda Sim Cancel reply

Your email address will not be published.

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