This is a problem that I have been working on for the last two days and have finally come up with a reasonably good solution/outcome. Many people seem to want to do this, especially small businesses with an extensive list of customer names or a product list that is connected to VLOOKUPs. They want to be able to select a name from a validation drop down list and have all the other variables auto change...which many are able to do...but what if there are 100s or even thousands of names within the list? You want to be able to enter the first letter and have the list automatically narrow-down. The following will achieve pretty much exactly that:
I will assume your list data is in a separate worksheet or workbook.
Make a reference list to the list data on another worksheet. Name that reference list. Now in the cell DIRECTLY underneath the list (same column), create validation list...make source ="whatevernameyougavethereferencelist". You should now be able to use a simple form of autocomplete in the validation cell...but we're not finished.
You clearly don't want this unwieldy reference list (which could be 100s or 1000s in length) to appear on the page...so you should now hide all those rows.
Uh-oh, now the autocomplete won't work for the validation cell!!! Don't worry...this happened to me when I tried to hide the reference list (and I have no idea why). Here's how to get around it...follow all the instructions on this page:
One part will be cutting and pasting VBA code. Not actually very hard, I am a complete novice but still could do it...just follow the instructions carefully.
Now you should be able to double-click on the validation cell and be able to select from the list using the drop-down arrow. With the list open in the drop-down box...you can simply type the first letter you want e.g. "m" and the list will auto highlight the first "m" word. Type "m" then "a" and it will go to the first word starting with "ma" and so on. The validation cell will also be autocompleting with the nearest on the list.
You might find it useful also to play around with the preferences of the combo box. I found it useful to extend the size of the drop-down list to 15 rows instead of the automatic 8...I can see more names at once when I'm scanning our big list of 3000 customer names.