Dynamic Cascading Drop-Downs Using INDEX & MATCH

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
=Lists!$A$1:INDEX(Lists!$1:$1,COUNTA(Lists!$1:$1))


"Categories" is used in the data validation list source, for the first drop down (fig3 & 4).





SUB-CATEGORIES (DEPENDENT-DROP-DOWN)
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:

=INDEX(Lists!$2:$2,colChoice):INDEX(Lists!$1:$1048576,COUNTA(INDEX(Lists!$1:$1048576,,colChoice)),colChoice)

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

attachment for described solution.

Instead of:

Lists!$1:$1048576

why not:

Lists!$A:$A


Regards, TMS

Well, let me break this down the second index third INDEX function:

First off, I'm using index with three arguments (Table, Row, Column) to return a reference to a cell. The reference will bottom-out my second drop down menu. The overall goal is to return a reference to the bottom-most cell of the column matching the user's choice (from the first drop down).

=INDEX(Lists!$2:$2,colChoice):INDEX(Lists!$1:$1048576,COUNTA(INDEX(Lists!$1:$1048576,,colChoice)),colChoice)


Table argument(Lists!$1:$1048576)
The table I'm interested in sits in the Lists worksheet, and I don't know how many rows and columns will be used. To allow for and unknown number of columns, I'll just assume the table starts in the upper left cell and pass in the entire sheet. That's what the Lists!$1:$1048576 does for us, it passes in all rows (entire-rows).

The second use of the index function highlighted in blue, returns an entire column (the one matching the users selection "colChoice"). By leaving the row argument blank (INDEX(Lists!$1:$1048576,,colChoice)) - we tell index to return the entire column.

In words: INDEX(Lists!$1:$1048576,,colChoice) means "return to me the entire column from the worksheet named list, whose column number is colChoice". By including that entire column in the COUNTA() function, we return the count of non-blank cells in the colChoice column (Since the column starts in the first row, this count is the row number of the bottom-most cells we are looking for -- assuming we have no blanks in the drop-down menu. A safe assumption if we build the list in the first place with that in mind).

SO, this blue highlighted section returns the row number we need.

Putting this together, we have:

=INDEX(Lists!$2:$2,colChoice):INDEX(from-entire-Lists-worksheet, Bottom-most-row#-with-text-in-colChoice-column, colChoice-column#)

And since we have indexed the entire sheet, we will get a reference like cells(bottom-row,choosen-column).address on the right side of the colon operator to complete our drop-down list