Excel Drop Down list with Search Suggestion
Step 1 – Configuring the Search Box
In this first step, I
will use a combo-box and configure it so that when you type in it, the text is
also reflected in a cell in real time.
Here are the steps to
do this:
1. Go to Developer Tab –> Insert –> ActiveX Controls –>
Combo Box (ActiveX Control).
o There is a possibility you may not find the developer tab in the
ribbon. By default, it is hidden and needs to be enabled. Click here to know how to get the
developer tab in the ribbon in Excel.
2. Move your cursor to the worksheet area and click anywhere. It
will insert a combo box.
3. Right-click on the Combo Box and select Properties.
4. In the properties dialogue box, make the following changes:
o AutoWordSelect: False
o LinkedCell: B3
o MatchEntry: 2 – fmMatchEntryNone
4. Go to Developer tab and click on Design Mode. This will enable
you to enter text in the Combo Box. Also, since cell B3 is linked to the combo
box, any text that you enter in the combo box would also be reflected in B3 in
real-time.
Step 2 – Setting the Data
Now that the search
box is all set, we need to get the data in place. The idea is that as soon as
you type anything in the search box, it shows only those items that have that
text in it.
To do this, we will
use
·
Three helper columns.
Helper
Column 1
Put the following
formula in cell F3 and drag it for the entire column (F3:F22)
=--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))
This
formula returns 1 when the text in the Combo Box is there in the name of the
country on the left. For example, if you type UNI, then only the values
for United States and United Kingdom are 1 and all the remaining values are 0.
Helper
Column 2
Put the following
formula in Cell G3 and drag it for the entire column (G3:G22)
=IF(F3=1,COUNTIF($F$3:F3,1),"")
This
formula returns 1 for the first occurrence where Combo Box text matches the
country name, 2 for the second occurrence, 3 for the third and so on. For
example, if you type UNI, G3 cell will display 1 as it matches United States, and G9 will
display 2 as it matches United Kingdom. The rest of the cells will be blank.
Helper
Column 3
Put
the following formula in cell H3 and drag it for the entire column (H3:H22)
=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")
This
formula stacks all the matching names together without any blank cells in
between them. For example, if you type UNI, this column would show 2 and 9
together, and rest all cell would be blank.
Creating
the Dynamic Named Range
Now
that the helper columns are in place, we need to create the dynamic named
range. This named range will only refer to those values that match the text
entered in the combo box. We will use this dynamic named range to show the
values in the drop-down box.
Note: In step 1 we entered
DropDownList in the ListFillRange option. Now we will create the named range
with the same name.
Here are the steps to create it:
1. Go to Formulas –> Name Manager.
2. In the name manager dialogue box click
New. It will open a New Name dialogue box.
3. In the Name Field enter DropDownList
4. In the Refers to Field enter the formula:
=$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)
Step
3 – Putting the VBA Code to Work
We
are almost there.
The final part is to write a short VBA code. This code makes the drop down dynamic such
that it shows the matching items/names as you are typing in the search box.
To
add this code to your workbook:
1. Right click on the Worksheet tab and
select View Code.
2. In the VBA window, Copy and Paste the
following code:
3. Private Sub ComboBox1_Change()
4. ComboBox1.ListFillRange = "DropDownList"
5. Me.ComboBox1.DropDown
End Sub
That's
it!!
You
are all set with your own Google type Search bar for a drop-down.
For
a better look and feel, you can cover cell B3 with the Combo Box and hide all
the helper columns. You can now show off a little with this amazing Excel
trick.
Posting Komentar