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