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.
Create a combo box by navigating to the Developer tab->Insert->ActiveX Controls
Right click on the combo box and go to property as shown below.
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.
Now click the Design Mode to get back to the normal Mode as shown below.
Now insert the following formula to cell F3 and drag the formula to the end of our list.
Now insert the following formula to cell G3 and drag the formula to the end of our list.
Now insert the following formula to cell H3 and drag the formula to the end of our list.
Now create a name range with the formula as shown below.
And press OK.
Now double click the drop down in design mode and paste the below code as shown below.
ComboBox1.ListFillRange = “DropDownList”
And press the Play Button.
As you could see the drop down is working and the value could be selected by clicking.
Hope this helped.