Ok, you can use a different type of drop down that does have an autocomplete
option.

Give your list of names on sheet1 a defined name like, say, Names.

On sheet2...
Goto the menu ViewToolbarsControl Toolbox
Click the Combo Box icon
Navigate to where you want the drop down to appear and "draw" the drop down
box by holding down the left mouse button while you drag the mouse.
Once you have the shape drawn, right click on the drop down
Select Properties
ListFillRange: Names
MatchEntry: 1 - fmMatchEntryComplete
Close the Properties window
On the Control Toolbox toolbar, hover your mouse over the top left icon. It
should say: exit design mode. Click this icon. After you click the icon it
should then say:design mode

Close the Control Toolbox toolbar

Now, when you click the drop arrow on the combo box you can start to type an
entry and it will "autocomplete".


--
Biff
Microsoft Excel MVP


"stremetzky" wrote in message
...
Yes it is a good solution but I have around 200 names in the Sheet1
and it's a bit awkward to search for the right name in the drop down
menu.

I would prefer that when I'd start typing, the excel would check the
list of names and give me suggestion for the name. I'd then confirm it
with Enter.

But thank you for your help.

stremetzky