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.
A series of Named ranges are created for a variety of item categories.
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:
This formula works like so...
In the Data Validation, be sure to turn off
the Error Alerts to make it easier to use.