Search Suggestion Drop Down In Excel

This post demonstrates how to create a google like dynamic search suggestions drop down list in excel.It is very helpful if you have a large number of values in your list and scrolling down the entire list is a painful task.

Dynamic search suggestions drop down keeps filtering the values that are displayed based on each input that you provide.

Let’s see how to create a drop down in excel with dynamically changing suggestions like below.




Capture

Step 1

Create a combo box by navigating to the Developer tab->Insert->ActiveX Controls

Capture

Step 2

Right click on the combo box and go to property as shown below.

Capture

Step 3

Now Go to Alphabetic tab in the properties window and set the following properties.

Set AutoWordSelect to False

Set LinkedCell to B2

Set MatchEntry to 2 – fmMtachEntryNone from drop down as shown below and close the properties window.

Capture

Now click the Design Mode to get back to the normal Mode as shown below.




Capture

Step 4

Now insert the following formula to cell F3 and drag the formula to the end of our list.

=–ISNUMBER(IFERROR(SEARCH($B$2,E3,1),””))

Capture

Step 5

Now insert the following formula to cell G3 and drag the formula to the end of our list.

=IF(F3=1,COUNTIF($F$3:F3,1),””)

Capture

Step 6

Now insert the following formula to cell H3 and drag the formula to the end of our list.

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)

Capture

Step 7

Now create a name range with the formula as shown below.

=Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$22,COUNTIF(Sheet1!H3:$H$22,”?*”))

Capture

And press OK.

Step 8

Now double click the drop down in design mode and paste the below code as shown below.

ComboBox1.ListFillRange = “DropDownList”

ComboBox1.DropDown

And press the Play Button.




Capture

As you could see the drop down is working and the value could be selected by clicking.

Capture

Hope this helped.

 

Share The Knowledge

Random Posts

Leave a Reply