Data Validation - enter first letter and find?

[Don]

Is there a way in Excel where you can start typing the name and it will try
and fill it out from a list?

I know in Access, you can create a form that when you enter the first letter
in a field it will try and find from a table the item you want. I also know
in my excel 2007 you can setup a Data Validation to link to a named list so
all you need to do is choose from a drop-down your item. The issue is that
the list I have for the names to choose from is almost 3,000 lines and people
want to either start typing and see a list of choices or have the dropdown
and type a letter to get to the start of that name?

Any suggestions would be helpfull

thanks

 

[Gord Dibben]

Don

If using a Data Validation List Dropdown, there is no Autocomplete function.

Debra Dalgleish shows how to use a Combobox in conjunction with the DV in
order to enable autocomplete.

http://www.contextures.on.ca/xlDataVal10.html

An alternative using a couple more cells comes from Ashish Mather

Please take a look at question 44 of the following link -
http://ashishmathur.com/replies.aspx

Another method......................

When setting up the List make sure your List is sorted alphabetically then
add a single letter at top of each group of items like A, B, C etc.

Then type a letter, say H, in the dropdown..........don't hit ENTER but
click on the arrow.

You will be taken to top of "H" items.

Thanks to Howard Kittle for this.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Don

If using a Data Validation List Dropdown, there is no Autocomplete function.

Debra Dalgleish shows how to use a Combobox in conjunction with the DV in
order to enable autocomplete.

http://www.contextures.on.ca/xlDataVal10.html

An alternative using a couple more cells comes from Ashish Mather

Please take a look at question 44 of the following link -
http://ashishmathur.com/replies.aspx

Another method......................

When setting up the List make sure your List is sorted alphabetically then
add a single letter at top of each group of items like A, B, C etc.

Then type a letter, say H, in the dropdown..........don't hit ENTER but
click on the arrow.

You will be taken to top of "H" items.

Thanks to Howard Kittle for this.


Gord Dibben MS Excel MVP