I have an Excel document pulling items from another xls as list items to populate a drop-down menu. The problem is, there are a LOT of choices. I can't find a way to quickly go to the area I want. (e.g. opening the drop-down list and pressing "b" doesn't go to the B's as it does for most normal drop-downs)
Is this possible? Or is there some way to autocomplete the drop-down once the user starts typing into it?
Although this is an old post, I would like to improve upon it since there is not a lot of great information regarding this. I wanted to add 3 important points:
When copying and pasting from your original list, use the Paste Special > Paste link option. This way when your original list is updated, this hidden list will also be updated.
Sometimes it's not possible to add this list directly above or directly below. For auto-complete to function, this is not necessary. The only necessary thing is that there is a data link between the drop down box and linked list of items below or above. e.g. let's say your drop down box is on field B5. Your list can start in B15, PROVIDED that there is a link of data between row 5 and row 15 either in columns A, B, or C (think of it as a chain that must be connected/continuous.). Data can be anything filled in these connecting cells, even spaces. So in our example, we just need to add in spaces in cells B6 through B14 in order for auto-complete to function properly. These cells of data can also be in A5:A15 or C5:C15. It sounds strange but I have done a ton of testing on this. Try it yourself!
The last point I wanted to make was the ability to type in a letter and have the drop down list auto-complete. When you are using a data validation drop down list as well as auto-complete, these are actually two different drop down lists of data. If you enter the cell and press the keyboard shortcut Alt + Down Arrow you will get the drop down list used in the validation you have set. If you type in a letter first, then press the Alt + Down Arrow you will get the Auto-complete drop down list (the list of items currently in the column, linked by data, hidden or not.)
Now here is where it gets a bit tricky and strange. If you have a large list of items, 1000 or over, after you type in that first letter or letters Excel does a background search of possible options within the column to auto-fill from top to bottom. If this is a list of alphabetized names and you type in a B, then press the Alt + Down arrow, the B names should appear rather quickly. (If not wait a second or two after entering your letters.) The number of seconds required for excel to locate these items will grow if this list is large and the name you are looking for is at the bottom of the list. So if you are looking for a name starting with X, you would enter the X, wait a few seconds, then press the Alt + Down Arrow, and your list should be displayed with those names starting with X. Trial and error is needed for the amount of time required as length of lists and machine speeds vary.
This seems like a lot of detail. However, if you are asking people to select a name from a large list and scrolling over 3,000 names in a drop down list is not an option, the information above will hopefully be helpful, as the end-user can save a lot of time locating items.
I made a video outlining the auto-complete feature in Excel showing highlighting the Don't Break The Chain method:
https://youtu.be/PVEzHbdHf1Y
Go To Tools>Options - Edit And Check Enable AutoComplete For Cell
Values Move Your List Of Items Is In The Range Immediately Above The
Validation Cell. Hide The Rows Above Your Validation Cell.
taken from here:
I have an alternate approach which uses Offset() to start the list from the point of text entered into the drop down cell.
For example with a list of countries typing Can will start the list with Canada. Just typing C will start with Cambodia.
Note that Error alert does need to be turned off for this to work.
The key is to use a formula like below as the list source.
=OFFSET(<I>CountriesListFirstCell</I>,MATCH(<I>DropDownCell</I>&"*",<I>CountriesListcolumn</I>,0)-2,0,COUNTA(<I>CountriesListColumn</I>)-MATCH(<I>DropDownCell</I>&"*",<I>CountriesListColumn</I>,0)+1,1)
