Multiple Categories

Dependent drop down in a single cell for multiple categories. How cool is that?

Here is the sample file demonstrating this technique:   AutoCompleteDataValidation(SortOf)3.xls

      A drop down presents a series of categories to choose from.

    Once a category is chosen, the same cell is used again to choose an actual item.
                                      That category remains active in this cell until the cell is cleared.


SETUP
:
A series of Named ranges are created for a variety of item categories.



The green cells across the top are named as a range simply List and serve as the initial drop down. 
Each column is another named range with category name + "List" tacked on.

NOTE: It is best if your categories are one-word. Spaces and special characters can be stripped out as we convert those words
            to Name Ranges, but why work that hard if you don't have to...?


We also create a couple of table ranges, one to encompass the entire set of options including the titles, called FullTable,
and another that encompasses the items only...called ItemTable.

These last two named ranges will be used in a very wild Data Validation formula:



=IF(COUNTIF(ItemTable,B2)=0, INDIRECT(TRIM($B2) & "List"), INDIRECT(INDEX(FullTable, 1, SUMPRODUCT((ItemTable=B2) * COLUMN(ItemTable))) & "List"))

This formula works like so...
  1. If the B2 is empty or contains a value that cannot me found anywhere in the ItemTable, display the main generic List.
  2. If a category has been chosen, it adds the word List to the category chosen, thus activating that named range as the new drop list.
  3. Once an item is chosen from category, the lone SUMPRODUCT() construct spots the value of B2 in the ItemTable to rediscover the category name. This way that category stays active in the cell until the cell is cleared.



In the Data Validation, be sure to turn off
the Error Alerts to make it easier to use.







Nothing says "thanks" like a steak dinner!

PayPal - The safer, easier way to pay online!
Comments