# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Auto complete Entries With Drop Down List

## getafixkwik

Hi all.
Is there a way to auto complete a drop down without using macro codes? 
lets say Sheet1 contain the drop down options and sheet2 have the validated drop down list of 200.
Thank you.

----------


## ChemistB

The short answer is "No" there is no way to have the dropdown options auotcomplete without using macros.

You can use this shortcut, however
Assuming the list is in alphabetical order, before each new letter in your list, insert the letter (i.e. A before the A's, B before the B's...)   Then, first enter the letter in the dropdown box, then when you open it up, it will initiate with the words beginning with that letter.  Make sense?

----------


## 6StringJazzer

If you use a combobox instead of Data Validation with List, then you will get autocomplete without macros. If you can attach a file and indicate where your list of items is I can demonstrate.

----------


## getafixkwik

to ChemistB, can you make sample file? thanks
to Jeff, i use a combo box before, is this by using the developer mode? if so, i can't make it to work if the developer mode is off or non existing in another computer. now back to my problem, say if i use a combo box for the whole column (maybe 300 or 400 cells) does this mean that i have to create that much combo box too or it can be drag down and copy them? 
Thanks for the quick reply.

----------


## 6StringJazzer

You insert the combobox by using Developer mode, but that is just to insert it. You do not have to use Developer mode to use it--in fact, it will not work if Developer mode is on.

In the attached file the list of items is found on on sheet "Country List". The list in column A is a named range called CountryList.

On Sheet1, I followed these steps:

Enter Developer modeClick Insert and select the combobox icon from ActiveX controls (the bottom half)Drag a combobox onto the sheetRight-click on the combobox and select PropertiesFind the ListFillRange property and enter CountryList for its value. This tells the combobox use the named range CountryList as the list of items.Exit Developer mode

Now you can begin typing in the combobox and it will autoselect the item nearest to what you type. When you follow the above instructions no macro is necessary.

By the way, please do not ask other members to provide a sample file to answer your question. As the person asking, you should be the one to provide a sample file. Then we can modify your file to show a solution.

----------


## ChemistB

Attached is a list of girl names set up the way I suggested in A2:A2310.  In C1, I entered G because I want to peruse names that begin with G.  I think the combobox may be the way to go with this.  Kudos Jeff.

----------


## getafixkwik

Thank you for helping
ChemistB: the attachment reflect only drop down that validate A1:A2310 data. cannot be type over and auto complete, or i am missing something
Jeff: combo box might be one of the solution here. can i copy it to make C2:C1000 into this format? if so how do i do it? and is the appearance display combo box not a regular blank cell until selected to display the box?
both your input is much appreciated. Thanks

----------


## 6StringJazzer

> can i copy it to make C2:C1000 into this format? if so how do i do it?



Right click on the combobox. Select Properties. Under the ListFillRange property change CountryList to C2:C1000.




> is the appearance display combo box not a regular blank cell until selected to display the box?



I am not sure what you're asking here, but the combobox is a separate control, not a cell. Before you were using Data Validation with a List, which is a cell. Then when you select the cell, the dropdown becomes available. A combobox is not a cell, although you can link it to a cell so whatever is selected in the combobox appears in the linked cell. In the Properties list, set LinkedCell to whatever cell you want to link. You can also adjust the size and position of a combobox to fit on top of a cell if that's the appearance you want.

----------


## ChemistB

Concerning my sheet, it is the best you can do with Data Validation.  The inclusion of the letters of the alphabet into the list allow you to open the list at whichever letter you want thus reducing the scrolling required.  It does not do any sort of auto complete.

Concerning your question about copying the combo boxes.  If you are the combo box is within the cell, if you go into Design mode, you can copy and paste the cell and the combo box will copy also.

----------


## getafixkwik

combo box is the likely answer. can i fill C1:C1000 with combo box with country list in drop down choice? (from sample or its not use that way?) if its possible, then how do i fill its property if i use calibri #12 font and column width of 50. with default size of a cell. seems weird to ask those questions. really don't have the basic knowledge, mostly i just follow example. thanks again jeff and thank you too ChemistB

----------


## getafixkwik

perhaps this is not the solution to this help. since i just want the said column to be a typing text cells. auto complete could have been a great help (If there's any).discounting drop down since the list is long, it will be inconvenient. my apology.

----------


## ChemistB

I'm not sure what the issue is?   As I said, you can copy the combobox to 1000 cells without an issue.

----------


## getafixkwik

Hi again ChemistB, actually i'm turned off with the appearance, and how i replace one inconvenience for another. or i'm just ignorant of the many uses.
> honestly i looks awful hehehe but this tread is very much solved. i'll mark solved after few Q&A if it's ok with you
> like after filling one combo and proceed to the next combo. down arrow or enter key won't jump to the next box. the mouse have to point to the next box for another input. 
> can you let the combo box disappear after your work (nice if it look like the normal spreadsheet)? and does the box also show in print (no printer here)?
> can't be filter.  
> manually designing the box is difficult (to match the cell size or there are easier ways to it (i zoomed it to 200%) 
 i hope you can give me more tips on this
 Thanks

----------


## ChemistB

after filling one combo and proceed to the next combo. down arrow or enter key won't jump to the next box. the mouse have to point to the next box for another input.    TRUE
> can you let the combo box disappear after your work (nice if it look like the normal spreadsheet)? and does the box also show in print (no printer here)?    You can remove the ARROW (DropButtonStyle =plain but  the gray box will always be there.
> can't be filter.   TRUE
> manually designing the box is difficult (to match the cell size or there are easier ways to it (i zoomed it to 200%) Fair enough

----------


## csoucier

Great and simple fix that worked well for me. Thanks!

----------


## stuw222

works in excel but when you put it in teams it stops working any ideas why?

----------


## AliGW

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

