Here is the sample file demonstrating this technique: AutoCompleteDataValidation(SortOf).xls
Instead of one LONG list of names, we organize our names into alphabetic lists, then we name them AList, BList, etc, like so:
We'll give the cells A1:Z1 the name range List.
Then, we'll give each of the lists of names their own name...A2:A8 is named AList and B2:B8 is named BList, etc.
Then we use the same trick used in Dependent Drop down boxes to create changing lists from previous drop downs, except we're going to do it all in one cell. Here's the setup:
In the formula: =INDIRECT(LEFT(TRIM($B2),1)&"List")
...we are using a TRIM() function in case someone ever types a space into the cell, the original List will be able to reassert itself.
In the Data Validation, be sure to turn off
the Error Alerts to make it easier to use.
We use the basic named range List to present the values
across the top of the series of lists.
Then we take the first letter of the list chosen,
add the word "List" to get the new value of AList or BList, etc,
causing the same cell to now present a different set of values
when the drop down is opened a second time.