I dug this up after reading Jbeaucaire's thread (link here). This is just another way to solve that problem using Index and Match. I like learning numerous ways to do things like this, if you do too, then please read on.
Create the top Categories as a header row above the associated subcategories (fig1).
TOP CATEGORY (FIRST-DROP-DOWN)
Create a named range for the top Categories (header row). Using Index, we can create a dynamic range (expands / contracts when you add / remove columns). The Refers-To box for Categories reads
"Categories" is used in the data validation list source, for the first drop down (fig3 & 4).
The second drop down menu, is defined by the users choice in the top category. A second named range, Sub_Categories provides the dynamic range needed for that data validation list (fig5)
The data validation for this cell is similar to the top category drop down, it is simply the name "sub_categories" (sorry, only 5 figures per posting).
In this version of the solution, the named formulas do the work. As shown in fig2, Sub_Categories makes use of the index function to define a subset of a column. The Refers To field for Sub_Categories reads:
colChoice is a third named formula, added just to make sub_categories more readable. colChoice is a standard use of the Match function used to "match" the users top category selection with the corresponding column on the "Lists" worksheet (also shown in fig2).
colChoice = Match(Selection!$C$2,Lists!$1:$1,0)
If you are unfamiliar with the INDEX function, this may look a little confusing. The main idea is, the first instance of the Index function (to the left of the Colon operator), defines a single cell reference corresponding to the top of the selected choices (note, this the choices start in row 2). In this example, it will place return A2, B2, or C2.
The second and third instances are a bit trickier, but they function to return a reference to the bottom cell (last) of the appropriate column. They will evaluate to either A7, B6, or C10 in this example. The entire formula will return one of the following (again, in this example) A2:A7, B2:B6, or C2:C10. Note, if you add or remove choices from any of the list, this function will expand/contract to reflect the change!
For a very good explanation of INDEX used to create named formulas, read up here.
My example is attached to the next post