Multiple Name Lists

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.

Once a name has been chosen, the same list will remain active in that cell, the "C" names or "B" names, etc. To get the original options back, press Delete to clear the cell.

Nothing says "thanks" like a steak dinner!

PayPal - The safer, easier way to pay online!