how to create a dynamic drop down list in excel

[dariusd77]

hope so one can help me out. I know how to create a drop down list, and I know how to do it so that as a person types the list will search and find what they are looking for..

But my problem is it seems it doesn't work with a large group of Data, when to me that is when it is most useful.
I have a list of over 40,000 parts. However when i create the dropdown list..it will not do the search..I have to type the full name of the part in..That kinda defeat the purpose.. Anyone have any ideas.
here is how i been creating the list.

I would sort the list in alphabetical order, then highlight all the table and give it a name in the name box.
then i would hide all the rows of list of data.
Then just below the list i would select the cell go to validation = allow list, source = name of list.
this create the drop down list, and also allow you to do the typing and search feature as well.

Any suggestion of a better way of doing it? that does not include VBA or using any control boxes.
Oh and the list has more list where as they search and choose the part, it also populates the cost and price.


Thanks for any help you can offer

 

[L. Howard]

Perhaps try this.

With your list in alphabetical order, at the top of the A's insert a row and a single "A", do the same for each letter in the alphabet to your entire list.

Now when you want to find a part that starts with say, an "R", type in the drop down cell the letter "R" AND DO NOT HIT ENTER.

While the cell is still active, click the down arrow of the drop down. It will take you to the top of the "R" named parts.

Now you have only to scroll the R's to make your final selection.

Regards,
Howard

 

[Smitty]

Noting that you don't want controls, ActiveX ComboBox controls do have AutoComplete functionality, and work great if you are going to have just a few of them.

 

[TrumpExcel]

Have a look at this.. might be helpful

Create a Drop Down list with Search Suggestions in Excel