Forum Discussion
SEarch function in data validation list
Hi Team ,
I need to add a search function to my data validation list . I see options on youtube but I cant use the filter in FX . Is there a different way of doing it . I have 210 things in dropdown so will be easier to search it .
I need search function in E7 and E9
Search is coming to Data Validation: see Speed up data entry and validation with AutoComplete for dropdown lists in Excel for Windows
In the meantime, you can use a combo box, as in the attached version (Windows only)
4 Replies
Xeryar here is another option but maybe not as good as HansVogelaar ? I worked on it but was called away but figured I post anyhow. BTW, since you say you can't use FILTER does that mean you are NOT using Excel 365? Which version are you running? Just in case you just couldn't get FILTER to work I included it in the attached. I also included a macro based solution but that has some glitch with typing in the cell and immediately clicking the drop down.
Search is coming to Data Validation: see Speed up data entry and validation with AutoComplete for dropdown lists in Excel for Windows
In the meantime, you can use a combo box, as in the attached version (Windows only)
- Can you please share the steps how did you do it
I used named ranges as source for the two data validation dropdown. You can see their definition in Formulas > Name Manager.
They are dynamic, so they will be adjusted automatically if you add items to the lists or remove items from them.
I then added an ActiveX combo box to the sheet with the dropdowns, and named it TempCombo.
I added code in the worksheet module for the Worksheet_SelectionChange event, based on the code in Data Validation Combo box Click
The only change I made was to take into account that the source of the validation dropdowns is on another sheet,
The code will automatically show the combo box on top of the data validation cell when you select it, and hide it otherwise.
