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 the list.

DataValFilter01

When cell B3 in this example is blank, a long list of names shows in cell C3. Instead of showing all the names, you can type a letter (or letters) in cell B3. This runs a macro, to show a short list of names in the drop down, based on the starting letter(s).

DataValFilter02

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.

_______________

 

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

12 comments to Shorten Data Validation List With Excel Filter Macro

  • dermotb

    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.

  • Martin

    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

  • Martin, can you email me a sample of what you're trying to do? ddalgleish AT contextures.com

  • Prakash Mainali

    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

  • Prakash Mainali

    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"

  • Erik

    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.

    • @Erik, there is a link for the sample file in the last paragraph of this blog post. You can download the file and see how it works.

      • Erik

        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

  • Narender Makhijani

    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.

  • Hi Debra,

    I click on the link to get the zipped sample file but it says "not found"....

    Thanks

    Ananda

  • [...] previous drop down to show only the 65 records with the same prefix. I have found a method here: Shorten Data Validation List With Excel Filter Macro | Contextures Blog but this seems far more complicated (?) than I need. Can anyone suggest a simpler method of [...]

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>