Excel auto find drop down menu
Create a Named range
To manage the named ranges, you can go in Formulas > Name Manager. You can also use defined range by their name in Excel formulas.
To create a named range in excel, you can select a column of data then right click then select define name the name will be the name of the range and how it will be referred to. The refers to is the range itself and is auto populated with the range of selected cells when clicking on define name
To get a dynamic named range you will need to replace the refers to of the named range by this kind of formula (for example if values are in the A column):
This formula will start looking at value from
A2 to the index (the coordinates) of the last non-empty cell (up to 100 in here).
It will only refers to the populated cells in the dynamic named range.
Get other information from the entered item
- You have a range of value with a defined name:
listwith all the values to find.
- You have the case where the search value is entered in
Then you can add this formula in the cells next to
C4 to map the cell using what has been entered in
MATCHfunction will match the
ROWof the entered value (here
C4) and the
Listvalue to get the right one.
ADDRESSfunction will map the found value and its relative position. (Used with
ROW, COLUMNyou can modify the address you get).
INDIRECTfunction print the value of the input coordinates (the
"tab_name!"where the value is, and the address of the found value).
2. Another Example
Or you can use this formula which will look in
List if it finds the value in
Have a google like search
Here is a sweet example that requires 1 column with the values and 3 helping columns, and a cell that will be used to do the Google-like search:
|E. Available values||F. criteria matching||G. Occurrence count||H. Found values|
- Column #1 : Available Values you add the values that will be looked at
- Column #2 : Criteria matching you add this formula:
This formula returns 1 if part of what is in cell
E3 in the Available values column is also in cell
B3, the search cell.
- Column #3 : Occurrence count you add this formula:
This formula starting at
F3 the criteria matching look if the criteria matching is 1 and count how many there was since first cell (
- Column #4 : Found Values stack all the criteria matching values with this formula:
G3 in the Occurrence count column. It works with
INDEX looking for occurrence.
IFERROR will show the corresponding value indexed, or nothing.
You can use this formula to create the dynamic range from the found values in
The name will be used for the combobox (dropdown in developer > insert > activeX). Here are the properties to look for:
- AutoWordSelect: False
- LinkedCell: B3
- ListFillRange: name of the created named range
- MatchEntry: 2 – fmMatchEntryNone
B3 is the searching cell, it will print the result of the search.
If you haven’t change the name of the combobox, the default one should be
ComboBox1 and you can copy and paste that into the VBA part of your sheet:
Private Sub ComboBox1_Change() 'DropDownList is the name of the created Named range ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub
ComboBox1_change() overwrites the default attitude of the ComboBox object when changed.
You also need some lists for the comboBox to update automatically, one for the dropdown that will be the results of the matched value of the occurrence count.