SOLVED

SEarch function in data validation list

%3CLINGO-SUB%20id%3D%22lingo-sub-3095389%22%20slang%3D%22en-US%22%3ESEarch%20function%20in%20data%20validation%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3095389%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%20%2C%26nbsp%3B%3CBR%20%2F%3EI%20need%20to%20add%20a%20search%20function%20to%20my%20data%20validation%20list%20.%20I%20see%20options%20on%20youtube%20but%20I%20cant%20use%20the%20filter%20in%20FX%20.%20Is%20there%20a%20different%20way%20of%20doing%20it%20.%20I%20have%20210%20things%20in%20dropdown%20so%20will%20be%20easier%20to%20search%20it%20.%26nbsp%3B%3CBR%20%2F%3EI%20need%20search%20function%20in%20E7%20and%20E9%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3095389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3095615%22%20slang%3D%22en-US%22%3ERe%3A%20SEarch%20function%20in%20data%20validation%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3095615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1242665%22%20target%3D%22_blank%22%3E%40Xeryar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESearch%20is%20coming%20to%20Data%20Validation%3A%20see%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fspeed-up-data-entry-and-validation-with-autocomplete-for%2Fba-p%2F3072083%22%20target%3D%22_blank%22%3ESpeed%20up%20data%20entry%20and%20validation%20with%20AutoComplete%20for%20dropdown%20lists%20in%20Excel%20for%20Windows%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20meantime%2C%20you%20can%20use%20a%20combo%20box%2C%20as%20in%20the%20attached%20version%20(Windows%20only)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3095892%22%20slang%3D%22en-US%22%3ERe%3A%20SEarch%20function%20in%20data%20validation%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3095892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1242665%22%20target%3D%22_blank%22%3E%40Xeryar%3C%2FA%3E%26nbsp%3Bhere%20is%20another%20option%20but%20maybe%20not%20as%20good%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3F%26nbsp%3B%20I%20worked%20on%20it%20but%20was%20called%20away%20but%20figured%20I%20post%20anyhow.%26nbsp%3B%20BTW%2C%20since%20you%20say%20you%20can't%20use%20FILTER%20does%20that%20mean%20you%20are%20NOT%20using%20Excel%20365%3F%26nbsp%3B%20Which%20version%20are%20you%20running%3F%26nbsp%3B%20Just%20in%20case%20you%20just%20couldn't%20get%20FILTER%20to%20work%20I%20included%20it%20in%20the%20attached.%26nbsp%3B%20I%20also%20included%20a%20macro%20based%20solution%20but%20that%20has%20some%20glitch%20with%20typing%20in%20the%20cell%20and%20immediately%20clicking%20the%20drop%20down.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 

4 Replies
best response confirmed by Xeryar (Occasional Contributor)
Solution

@Xeryar 

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)

@Xeryar here is another option but maybe not as good as @Hans Vogelaar ?  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.  

Can you please share the steps how did you do it

@Xeryar 

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.