autocomplete from specific list

[stremetzky]

Hi,
I have Sheet1 with different names in a column.
In the Sheet2 I have to type in the names in certain cells. What can I
do that I don't need to write the whole name again but after I beggin
typing excel would check the list of names in Sheet1 and help me to
autocomplete it.

Thank you for your suggestions,
stremetzky

 

[T. Valko]

How about this...

Use drop down lists on sheet2 then you don't have to do any typing at all,
you can just select the name from the list.

Watch this 5 minute video:

 

[stremetzky]

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

 

[Gord Dibben]

Autocomplete doesn't work that way.

Only looks at cells above or below in the one column in which you are entering
data.


Gord Dibben MS Excel MVP

 

[T. Valko]

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 View>Toolbars>Control 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".

 

[stremetzky]

Thank you very much for suggestions, I managed to solve it with your
help.

stremetzky

 

[stremetzky]

Thank you very much for suggestions, I managed to solve it with your
help.

stremetzky

 

[stremetzky]

Thank you very much for suggestions, I managed to solve it with your
help.

stremetzky

 

[T. Valko]

You're welcome. Thanks for the feedback!