Dependent drop downs normally occur in adjacent cells, this technique is for a dependent drop downs in the same cell
Here is the sample file demonstrating this technique: AutoCompleteDataValidation(SortOf)2.xls
UPDATE: Now Excel 2007+ compatible.
To start, we simply need a list
of Alphabet categories,
and one long list of names/options/whatever.
Once the list is created, be sure to sort it
so that it is alphabetized.
The alphabet list is static, so a flat
named range of AlphaList is created
for those beginning options.
(This step could be skipped
if you don't want this first list to appear.
If you skip making this named range,
Then the drop down will be empty
until you type in a letter to
activate the second list.)
The names are an ever-changing list in column B, so a dynamic name range NameList is used, the formula shown is self-adjusting.
As you add/subtract names from the NameList, always remember to sort the final list.
This is critical!
Select the first target cell that will receive the DV formula later.
This is to insure we get the "starting point" correct on the relative portion of creating the NAMED FORMULA. (see next step)
Now we create a named formula to the heavy lifting.
Open the Insert > Name > Define window and create a named formula called Name_Selector and using the following formula for the Refers To:
AlphaList, INDEX(NameList, IF(ISERROR(MATCH(LEFT(B2) & "*",
NameList, 0)), MATCH("*", NameList), MATCH(LEFT(B2) & "*", NameList,
0))) : INDEX(NameList, MATCH(LEFT(B2) & "zzz", NameList)))
If you opted to NOT create the NameList,
then use this shorter formula for the Source:
=INDEX(NameList, IF(ISERROR(MATCH(LEFT(B2) & "*",
NameList, 0)), MATCH("*", NameList), MATCH(LEFT(B2) & "*",
NameList, 0))) : INDEX(NameList, MATCH(LEFT(B2) & "zzz", NameList))
Next we setup the data validation for that cell.
Bring up the DV dialog window and put in the settings:
Be sure to uncheck the error alert box.
This allows you to actually type in a first letter
if so desired.
Nothing says "thanks" like a steak dinner!