Excel Search In Data Validation Drop-Down Lists

Excel Searchable Data Validation Drop Down Lists


        Searching by one or several characters in data validation lists, greatly simplifies the user's task. For this,we created searchable drop down lists on the automated 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.

We followed these steps to search in the data validation drop down 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 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:
Private Sub Worksheet_Deactivate()
    Range("A2:C" & Rows.Count).Sort Range("A2"), xlAscending
    End Sub
excel sort ascending

 As an example, we selected cell A17 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&"*"),)

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

excel search in data validation list
excel search in drop down list
The result is excellent !
excel find in data validation list

No comments:

Post a Comment