In this article you will learn how to create a searchable excel drop down list in excel. Just assume you have a lengthy list in excel data validation and if you must select any item then it will take some time because you must scroll it to find your desired item in the excel data validation list.
Here I have created a solution of this and that is Searchable Drop-Down List. Just type the any keyword on an excel cell and click on drop-down arrow, you will see the drop-down list related your keyword.
Below are the steps to create drop down lists and make then searchable for multiple cells
Let’s say we have a country list on a worksheet (“Sheet1“) in column A.
Put the below given formula on column B,C and D
- Put the headers – “Search” on cell “B1“, “Frequency” on cell “C1” and “Final List” on cell “D1“
- Put the formula “=IFERROR(SEARCH(INDIRECT(CELL(“address”)),A2),0)“on cell “B2“.
- Put the formula “=IF(B2=0,””,COUNTIF($B$2:B2,”>0″))”on cell “C2“.
- Put the formula “=IFERROR(INDEX(A:A,MATCH(ROW(B1),C:C,0)),””)“on cell “D2“.
- Fill the formulas till the end of list.
- Go to Formulas Tab and click on Define Name
- New Name window will be opened.
- Give the Name as Country_Name.
- Put the formula “=OFFSET(Sheet1!$D$2,,,COUNTIF(Sheet1!$D:$D,”*?”)-1)” in “Refer to” box.
- We will create our searchable drop-down on “Sheet2“.
- Go to Visual Basic Editor (Press Alt+F11)
- Double click on “Sheet2“
- Paste the below given code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
Below is the snap set of Visual Basic Editor–
- Now go to the “Sheet2” and select the range of cells where you want to create searchable drop-down.
- Open the data validation dialog box (use shortcut key – Alt+D+L)
- Select the list in allow drop-down.
- Click in “Source” box and press F3 to open the Paste Name window.
- In the Paste Name window select the Country_list Name and click on OK
- Name will be reflected in the Source box.
Note: You can type the name in the source box directly like “=Country_list“
- Now go to the Error Alert tab of data validation window.
- Uncheck the “Show error alert after invalid data is entered” check box.
- Click on OK button.
- Searchable drop-down has been created. if you click on drop-down arrow, it will show the complete list of countries.
- If you will type some keyword and click on drop-down arrow, then it will show the related list.
Click here to download this excel file
