Autocomplete dropdown list, NOT data validation or ActiveX

Let me be the first to say... start over with ActiveX controls.

It's always better to redo mistakes than it is to add plumbing to work around the errors. The built-in functions Excel has to offer would always be preferable.

You can get a data validation list that will work pretty close to what you want if the source data is sorted and you insert the letters before each group.

For example:

Data Range

	A
2	A
3	AirHead
4	Angry
5	B
6	Badly
7	Bladder
8	C
9	Creepy
10	D
11	Doofus
12	Dumbo

Setup the drop down list using A2:A12 as the source.

Then, assuming cell C2 contains the drop down list...

Type the first letter of the group of interest but don't hit enter, instead, click the drop arrow and the list will automatically scroll to the letter you typed. Then you can scroll as needed and make your selection without having to scroll through the entire list.

Good deal. Thanks for the feedback!

You can eliminate the numbering formula and create the source list using another formula.

For example...

Let's assume the drop down list is setup in cell A5.

E6:E16 is the master list (all names) and the source for the drop down list will be the dynamic list in F6:Fn.

Data Range

	E	F
5	Master List	Dynamic List
6	Tom jones	John Lennon
7	Tony Valko	Bill Johnson
8	Tony Spam	Terry Johnson
9	Bill Jones	Nancy Johnston
10	Frank Sentak
11	John Lennon
12	Bill Johnson
13	Tim Jones
14	Lisa Babs
15	Terry Johnson
16	Nancy Johnston

This array formula** entered in F6:

=IFERROR(INDEX(E:E,SMALL(IF(ISNUMBER(SEARCH(A$5,E$6:E$16)),ROW(E$6:E$16)),ROWS(F$6:F6))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to enough cells so that it will return all the corresponding results.