Searchable drop down list in excel without vba
Show Hi As my excel version do not have filter, sort, unique functions I have to use other set of formulas to search the drop down list in data validation. A range of data is created with Code and BP name in column A & B under Master sheet where "Master" name range is set on column B and Data validation list is set without error alert at cell B2 and below under Entry sheet Below set of formulas copied from google search and modified :- 1) Master name range "OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)" 2) Data Validation List "OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)" When new BP name & code are added to the list of Master sheet, the list will be updated and sorted automatically in data validation list. If there are more than 100 names to search in drop down list, it would be more efficient to use dynamic search in drop down list. However, when I try to do dynamic search by "Trad" or "Co" in cell B2 in Entry sheet, the drop down list show nothing ?? Did I miss out anything in my formulas ? My objective is to make every row of data entry as dynamic search in drop down list data validation such as filtering search without using VBA and also do not use filter, sort, unique function as they work on office 365 and it will be refresh the drop down list for next row of entry. Appreciate if anyone can help to solve the above problem and thanks in advance Regards Len
We all use Google as a part of our daily routine. One of its features is search suggestion, where Google acts smart and gives us a list of suggestions while we are typing. In this tutorial, you’ll learn how to create a searchable drop-down list in Excel – i.e., a drop-down list that will show the matching items as you type. Below is a video of this tutorial (in case you prefer watching a video over reading the text). Searchable Drop Down list in ExcelFor the purpose of this tutorial, I am using the data of Top 20 countries by GDP. The intent is to create an excel drop down list with a search suggestion mechanism, such that it shows a drop down with the matching options as I type in the search bar. Something as shown below: To follow along, download the example file from here Creating the searchable drop-down list in Excel would be a three-part process:
Step 1 – Configuring the Search BoxIn 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:
Step 2 – Setting the DataNow 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
Helper Column 1 Put the following formula in cell F3 and drag it for the entire column (F3:F22) 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 RangeNow 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:
Step 3 – Putting the VBA Code to WorkWe 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:
That’s it!! You are all set with your own Google type Search bar that shows matching items as you type in it. 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. To follow along, download the file from here If you have enjoyed this tutorial, I am sure you would like the following Excel tutorials too: |