Excel Dropdown: Quick Picks?

Question

I have an Excel document that pulls items from another xls as list items to populate a dropdown menu. The problem is that there are many options. I can’t find a way to quickly move to the area I want. (for example, opening a drop-down list and pressing "b" does not go to B, as is the case for most normal drop-down lists)

Is it possible? Or is there a way to autofill the drop-down list as soon as the user starts typing it?

Answer 1

Go to "Tools"> "Options" - "Edit and Verify" Enable autocomplete for a cell

Values ​​Moving your item list is in the range directly above

Cell validation. Hide rows above your validation cell.

taken here:

http://www.ozgrid.com/Excel/autocomplete-validation.htm

Answer 2

Although this is an old post, I would like to improve it, because there is not much information in it. I wanted to add 3 important points:

• When copying and pasting from the source list, use the Paste Special > Paste link option. That way, when your original list is updated, this hidden list will also be updated.

• Sometimes it is not possible to add this list directly above or below. This is not necessary for automatic shutdown. It is only necessary that there is a data link between the drop-down list and the associated list of items lower or higher. for example, suppose a drop-down field is in field B5 . Your list may start with B15 , REPRESENT that there is a relationship between lines 5 and line 15 either in columns A, B, or C (think of it as a chain that needs to be connected / continuous). Data can be anything filled with these connecting cells, even spaces. Therefore, in our example, we just need to add spaces in cells B6 through B14 for auto- B14 to work correctly. These data cells can also be in A5:A15 or C5:C15 . This sounds strange, but I spent a ton of testing. Try it yourself!

• The last thing I wanted to do was the ability to enter a letter and an automatic drop-down list. When you use the data validation drop-down list and also automatically complete, these are actually two different data drop-down lists. If you enter a cell and press Alt + Down Arrow , you will get a drop-down list used in the scan you set. If you type the letter first, then press Alt + Down Arrow , you will get a drop-down list of autocomplete (a list of items that are currently in the column, whether they are data-related, hidden or not).

  Now that's where it gets a little complicated and weird. If you have a large list of items, 1000 or more, after entering the first letter or letters, Excel performs a background search for possible options inside the column to automatically fill from top to bottom. If this is a list of alphabetical names and you enter the letter B, then press Alt + Down Arrow , the names of B should appear pretty quickly. (If you do not wait a second or two after entering your letters.) The number of seconds required to search for these elements will increase if this list is large and the name you are looking for is at the bottom of the list. Therefore, if you are looking for a name starting with X, you must enter X, wait a few seconds, and then press Alt + Down Arrow , and your list should be displayed with those names starting with X. Judicial and error is required for the amount of time required to increase the length of the lists and the speed of the machine.

This seems like a lot of detail. However, if you ask people to select a name from a large list, and scrolling over 3,000 names in the drop-down list is not an option, information that we hope will be useful as the end user can save a lot of time searching for Items. I made a video outlining an autocomplete function in Excel showing the highlight of the Don't Break The Chain method: https://youtu.be/PVEzHbdHf1Y

Answer 3

I have an alternative approach that uses Offset() to start a list from a point in the text entered in a dropdown cell.

For example, with a list of countries typing, Can might start the list with Canada . Just type C start with Cambodia .

Please note that for this you need to turn off the error warning.

The key is to use the formula as shown below as the source of the list.

 =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)