autofill a validation list when user types letter 1

[anationalacrobat]

I could have sworn this was possible in earlier versions of Excel. I'm stuck on 2007 now.

I have a list of activities on one sheet that I would like to use as a range of options for data validation. In other words, we're presented with what looks like an html combo box, the down arrow is clicked and we see a list of options. Because there are so many, it would be nice if typing the first letter of the option you know you want would bring the list to the right order, alphabetically speaking.

I could have sworn this was possible in 2003 but I cannot seem to make it work in 2007. I also could have sworn that you could directly navigate to another worksheet in the workbook to select your options list but in 2007 you have to make the list a named range, AND there's no button on the GUI to tell you how to bring up the range, you have to type in "=", hit enter and there they are. Not very fun.

 

[anationalacrobat]

Actually, I should have said autocomplete, that's the right term.

Examples I'm finding online are implying I need to resort to VB. Would that really be the case?

http://www.dailydoseofexcel.com/archives/2005/02/16/limit-a-listbox/

 

[SkipVought]

Hi,

Autcomplete does not work in a Data > Validation -- LIST drop down.

You'll have to use a Control Toolbox Combobox, properly configured.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[anationalacrobat]

Not the answer I wanted to hear but that's not your fault. Thanks!

 

[Lilliabeth]

>>Autcomplete does not work in a Data > Validation -- LIST drop down.

But autocomplete will work for a list item after that list item has been used once, right?

In Excel 2003, like 2007, if the list was on a different worksheet, it had to be referred to by range name in the Data Validation dialog box.

--Lilliabeth

 

[anationalacrobat]

Ok, I have access to a terminal server here with 2003 installed so I'm checking what I thought I knew against that to make sure I'm not going insane. Here's what I've found:

1. I was wrong about the different sheet thing, you had to use a named range even in 2003.
2. Autocomplete works in both so long as the selection exists at least once in your column. If you used "cat" in the cell above, typing "c" will get you there.
3. By default, using the listbox means you simply cannot enter partial words and get away with it in 2003 but 2007 lets you do it. I'm not seeing the option to enforce those values and those values alone. Go figure.