Data Validation: Navigating list by pressing first letter ..

[jaymanwani7]

Hello Friends,

I have created a sheet, which contains names of customers in alphabetical order and the customer just chooses his name from the list and all his data his shown. The problem is there are over 100 curtomers and scrolling through names is time consuming and customers dont favor it. Is there a way in data validation list which can allow me to navigate the drop down list faster by pressing the first letter or even typing a name and excel can auto-fill it based on the list.

Please let me know. Thanks for your time.

Best,
Jayant !

[teylyn]

Hi jaymanwani7,

you won't be able to do that with a data validation list. But if you use a listbox instead and a little VBA code that runs in the background, you can achieve autocomplete/autofilter functionality.

Have a look here: http://www.dailydoseofexcel.com/arch...mit-a-listbox/

[royUK]

A ComboBox from the Controls toolbox is the best option, it has a built-in auto-complete

[jaymanwani7]

Hi jaymanwani7,

you won't be able to do that with a data validation list. But if you use a listbox instead and a little VBA code that runs in the background, you can achieve autocomplete/autofilter functionality.

Have a look here: http://www.dailydoseofexcel.com/arch...mit-a-listbox/

....

Thank you for quick respone. Unfortunatley, I am good with excel but not good with VBA and I couldn't understand the VB code for boxes.

What do you reccommend? Can anyone upload a sample fil here. I know thats asking too much, but I really tried to understand what to do but couldn't.

I will highly appreciate if anyone can make sample excel file and I can use it as a tempelate.

Best,
Jay!

[Palmetto]

I will highly appreciate if anyone can make sample excel file and I can use it as a tempelate.

This won't serve as a template, but it will get you started in the right direction.

In the attached . . .

A list of items was created and named range created/assigned for this list (Name = MyList)
A Combobox from the Controls Tool Box was created and the named range uses as the source for the ListFIllRange property

The combobox is set to be invisible unless the selected cell is in the range A1:A10, in which case it becomes visible and the selected item is then written to the active cell. The combobox has its "match" property set to first letter so it will match list items as you type.

Please Login or Register  to view this content.

[Palmetto]

While cleaning up my subscribed threads, I thought it worthwhile to point you to an excellent approach developed by JBeaucaire. It works perfectly and seems to be exactly what you were looking for.

Auto Complete in a Data Validation cell technique

[Catherine1]

i am having the same issue.... not good with VBA
I have a list that i placed in validation but it is very tedious without the ability to quickly enter the letter or code if i know it
also note that other cells are linked to this one via a vlookup
The cell with the list is called "Child id" then this links to the next cell that will pull the adult ID and then the next cell that pulls the name