COmbo box and lookup list

[G Lam]

Hi, I have a vlookup cell to look up a list of 5500 items. Some times, users
may enter a wrong item and got nothing retunred. I am thinking of add a drop
down box for them to lookup the list in that case. However, 5.5K item is way
too much for the drop down. Does Excel have a function like Access that when
user enter first few characters, the drop down will show a list of items
have the same characters. How can I code that?
Thank you.
GL

 

[Rob van Gelder]

Excel has a "sort of" autocomplete feature.
It looks at cells above/below it to attempt an autocomplete. It doesn't
check many though. Certainly not the 1000s you're talking about.
I wrote the word test into cell a1 then wrote the word excel into cells
a2:a1000
typing the first few characters of "test" started autocomplete, with
different behaviours depending on how far away you were:

typing t autocompleted on A51, but didn't on A52, I had to type te to get it
to autocomplete.
getting autocomplete to start became more difficult after 50 cells
clearance.

I don't know of a another simple way to autocomplete.

 

[Dave Peterson]

saved from a previous message:

But you could use a combobox from the Control Toolbox toolbar. There's a
property that you can set telling it to look for a match.

You could plop that combobox right over the same cell.
rightclick on the combobox and select properties.
look for listfillrange and use the same range as you used for Data|Validation
(or if you typed in the values in the DV dialog, put those values on a different
worksheet (hidden, maybe).

Look for linkedcell and type the address of the cell you're using to hold that
combobox.

Look for Style and change it to fmstyledropdownlist
(prevents users from typing anything they want)

Look for matchentry and try fmmatchentrycomplete (or fmmatchentryfirstletter)
(depends on what you want).

When you're done, use the arrow keys to select that linked cell.
Format|cells|number tab
custom category
In the "Type box", put ;;;
(3 semicolons will hide the value in the cell, but you'll still be able to use
it in your formulas.)