Single Name List

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.

 




The cell presents a list of "Name" groups. A selection is made from the first list...

(Alternately, you could just type the letter
"C" or "F" and press ENTER.)


A selection is made from the first list...
 
 

Then an item is chose from that sub group
immediately in the same cell.

 
 
Even though an item has been chosen,
all the names in that group remain visible
as a subset of the main listing in the
drop down.

In this case, the "C" names are listed.

Once the cell is cleared, the original list is offered again.


SETUP:
   



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.

=Lists!$B$1:INDEX(Lists!$B:$B,MATCH("zzz",Lists!$B:$B))

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:

=IF(TRIM(B2)="", 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:

Allow: List

Source: =Name_Selector


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!

PayPal - The safer, easier way to pay online!


Comments