Forum Discussion
Using the Combo Box to search a data list
Good morning All, I am currently utilizing Data Validation in combination with Vlookup to (A) Search and autopopulate data within our spreadsheet (approximately 26,000 employees). We don't have th...
you say " but it keeps spilling. " which says to me you do have Excel 365 or at least the dynamic array functions. The auto-search feature in the data-validation box might be an excel online feature. But with dynamic arrays you can easily create a pseudo search:
basically lets say the look up list is in column A, the drop down box is in D1 and create a helper list in G1 = FILTER(A:A, ISNUMBER(SEARCH(D1,A:A)),"no matches")
and then set the data validation for D1 to be =G1# and remove the error option (i.e. allow non-matches)
then you type in D1 the first few letters to match and then click the drop-down and you will only see those that have those letter in it. (on a mac I believe you have to hit enter first then click the drop-down). It isn't "live" but still very helpful. here is a helpful link to walk you through if needed:
https://www.xelplus.com/searchable-drop-down-list/
basically lets say the look up list is in column A, the drop down box is in D1 and create a helper list in G1 = FILTER(A:A, ISNUMBER(SEARCH(D1,A:A)),"no matches")
and then set the data validation for D1 to be =G1# and remove the error option (i.e. allow non-matches)
then you type in D1 the first few letters to match and then click the drop-down and you will only see those that have those letter in it. (on a mac I believe you have to hit enter first then click the drop-down). It isn't "live" but still very helpful. here is a helpful link to walk you through if needed:
https://www.xelplus.com/searchable-drop-down-list/
Thanks for responding. I get off work at 5 30 pm
I will try your strategy as soon as I get off.
Lol- this could be a game changer....
I have 11 years of Federal experience as a Human Resources Benefit/Recruitment Specialist - but "only 1 year of experience - doing collateral duty as a Data Analyst. I learn something new almost every other hour...lol.
Before i arrived - they manually input everything. I learned and taught the Vlookup feature - and now our fields are auto-populated from our centralized database.
Now I will try your strategy - as to search on our existing Data Validation. The Vlookup is connected with our Data Validation.
