Home
Excel VBA Examples
How To Do 
Software 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:
➜ 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)
➜ 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 :
➜ 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 :
=OFFSET(Product!$A$2,MATCH($A17&"*",Products_1,0)-1,,COUNTIF(Products_1,$A17&"*"),)
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:
➜ 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)
➜ 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➜ 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 :
=OFFSET(Product!$A$2,MATCH($A17&"*",Products_1,0)-1,,COUNTIF(Products_1,$A17&"*"),)
As example ,our formula for cell A18 :
=OFFSET(Product!$A$2,MATCH($A18&"*",Products_1,0)-1,,COUNTIF(Products_1,$A18&"*"),)
✓ 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.
=OFFSET(Product!$A$2,MATCH($A18&"*",Products_1,0)-1,,COUNTIF(Products_1,$A18&"*"),)
✓ 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.
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. ↓
Read more ...
