How do I find an entry from thousands of entries in drop down lists in Microsoft Excel? E.g. Edit>Find?

Question

I created drop down lists in an excel worksheet and each drop down list contains several hundred entries. How can I search for a particular entry from thousands of entries from those drop down lists? I explored Edit> Find option, but looks like thats not the right way for this task.

Answer 1

How did you create these drop down lists? Did you link them to other cells? Or did you add each item manually? For a number of reasons, it makes a lot more sense to create lists on one sheet, hide that sheet and then link the drop down lists to that sheet. If that is true, you just have to find that sheet and you can use the regular find function there. If it isn't true, you will probably have to do some macro work. Have you ever worked with VBA before?

Each drop down list is an object and its list items are all accounted for in an array. You can create a subroutine to loop through all of the arrays looking for the values you are trying to find. Its actually fairly easy to do if you have a little bit of programming experience. If you don't you might have to outsource it.

Answer 2

When you click on the arrow that brings up the dropdown list... hit the letter key that applies to your search. That'll bring you to any entry beginning with that letter. If it's a number, select that number on your keyboard to bring you to that particular character on the list. That'll break down the choices somewhat. Unfortunately, you can only choose one letter or number.

But, you can just do the standard search on the page by clicking on "Ctrl F", then type in the full number, word or phrase. OR you can select the row or column you want to search and narrow your search that way. I would think this type of search would be more accurate for finding a specific thing rather than than the drop down method I suggested above.

Answer 3

The above answer is okay, but may be confusing to a novice and me, as well: "...brings UP the dropDOWN list..."??? ;)

It may depend on what it is you're looking for. Certain formulas, graphics, and text aren't "searchable" via Excel; at least, not in the way you'd think. For example, if you enter "text" into a "number" field, it not only may not display properly, but it may be impossible to "find".

Also, if there is "interceding" text, or "merged" columns or rows, that can throw off the "find" as well.

Unfortunately, this is one of those questions for which there is no easy answer, unless we can see the actual worksheet, which, obviously, we can't!

Any library or bookstore should have a decent "how to" book on Excel. Search the 'net, too!

Good luck!