Excel 2007 : Validation list lookup

**rock755** · 08-07-2010, 12:55 PM

Hello:
This is my first post so I wil try to be clear
I am creating a drop down validation list for a list of 2000 products.
When the user selects the drop down arrow the list appears in alphabetical order.
However when you type in a letter nothing happens.
The user must scroll down the entire list to find the product-very time consuming.
I want the user to be able to type in the first few letters of the product and have the drop down list take him there. For example to select coffee products if the user starts typing "cof" the drop down list will go to this entry.

Thank you

**Everstrivin** · 08-07-2010, 02:25 PM

I found that if you just use the activex control it automatically does this. Just assign the list of values to the rowsource property.

**Everstrivin** · 08-07-2010, 02:28 PM

Sorry in design mode you click on properties then click on the combobox then in listfillrange type the range where the values are such as A1:A12

**Palmetto** · 08-07-2010, 04:45 PM

With such a large list of items, you should consider breaking them into subcategories and use Dependent Lists.
Help on the topic is available here: Excel Data Validation -- Create Dependent Lists

Also, I highly recommend Jerry Beaucaire's Auto-Complete Data Validation. Jerry is one of Excel MVP's on this forum.

**rock755** · 08-08-2010, 01:40 PM

I went to Jerry Beaucaire's Auto-Complete Data Validation and my problem is solved.
Auto complete would be nice but I guess this is only available via Access 2007

Thank you

**Palmetto** · 08-08-2010, 07:37 PM

Auto complete would be nice but I guess this is only available via Access 2007

You don't have to use Access to have this functionality. In Excel, only the active-x combo box has the auto-match functionality. IMO, unless you really need VBA, the first choice should always be built-in features/functions and clever formulas like that give by JBeaucaire.