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.
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).
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)
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.
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.
Download the Sample File
To see the Excel macro that filters the orders and the data validation source, you can download the zipped Data Validation Advance Filter sample file. It's in Excel 2003 file format, and you'll have to enable the macros to test the file.
_______________
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.
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
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
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"