Excel Search In Data Validation List (Searchable Drop Down List)

excel searchable data validation list

Create Dynamic Searchable Drop Down List In Excel

Being able to search based on one or more characters in the data validation list greatly simplifies the user’s task, especially in drop-down lists with many values. For this, we created the searchable drop down lists on the invoice template that we created earlier.

We will use the searchable drop down lists to bring data from the Product sheet to the Invoice sheet. After the operations we will do, we will create dynamic searchable data validation lists that fetch data from another page. Namely, when a new product is entered on the Product sheet from which we receive data, it is automatically listed in the data validation lists on the Invoice sheet.

We followed these steps to create the searchable data validation list:
searchable data validation list  Firstly, we defined a new name “Products_1” in the Formulas_ Name Manager menu:
Products_1 =OFFSET(Product!$A$2,,,COUNTA(Product!$A:$A)-1)

searchable drop down list We opened VBA Editor Window by pressing Alt + F11.  Into the Worksheet_Deactivate procedure of Product sheet, we have added the following codes to sort as alphabetically the products on the sheet when we add products and leave the sheet :
Private Sub Worksheet_Deactivate()
    Range("A2:C" & Rows.Count).Sort Range("A2"), xlAscending
End Sub
excel vba sort order

excel search in data validation list We selected Range(A17:A33) cells on the Invoice sheet and entered the following formula in the Source section of the Data Validation_List menu: excel searchable data validation list=OFFSET(Product!$A$2,MATCH($A17&"*",Products_1,0)-1,,COUNTIF(Products_1,$A17&"*"),)
As example ,our formula for cell A18 : excel dynamic searchable data validation list =OFFSET(Product!$A$2,MATCH($A18&"*",Products_1,0)-1,,COUNTIF(Products_1,$A18&"*"),)

excel data validation error alert An important and not to be forgotten point is ; It is to remove the confirmation next to the text “Show error alert after invalid data is entered” in Error Alert tab in Data Validation window. Otherwise, we cannot create a searchable data validation list as we want. excel searchable data validation list
excel data validation error alert

When entered a letter or number in cell A17 and pressed the arrow icon to right of the drop-down list, the values ​​that started with that letter or number are listed on data validation drop down list. excel search in drop down list

A very useful invoice example has been created.

📥 Sample file can be downloaded here