When each item in a primary list is associated with a different collection of items in a group of secondary lists, you can use cascading lists boxes to manage those secondary lists.
For example, the first image at the right shows that Hats are sold in three states, and that Florida was chosen from that list.
In the second imge, the Coats item is about to be chosen from the Product list.
In the third image, with Coats chosen, the State cell has turned red, warning you that Coats aren’t sold in Florida.
Finally, in the fourth image, the State list box shows the five states in which Coats are sold, and New Jersey is about to be chosen.
You could use a primary-secondary list structure like this in many ways. For example…
- The primary list could be names of departments and the secondary lists could be the names of people who work in each department.
- The primary list could be the names of competitors and the secondary lists could be the regions in which each competitor is active.
- The primary list could be the names of vendors and the secondary list could be the products you buy from each vendor.
- Etc.
Then, once you choose a primary and secondary item, your report could use SUMIFS, COUNTIFS, AVERAGEIFS, SUMPRODUCT, array formulas, or other aggregation methods to return information about your selections from tables in your workbook.
The following description explains how to set up cascading list boxes like this using dynamic range names, and conditional formatting.
Begin the List-Maintenance Table
This image shows the complete layout for this cascading-list example:
There’s no need for the table to be on the same worksheet as the lists. I’m showing it close to the lists for convenience in this article.
This table is what I call a graycell table, for obvious reasons. The narrow gray rows and columns mark the boundaries of ranges used with the table..as you’ll see.
To begin, set up the table’s text and formatting as shown. Then define the three names that reference the ranges to their right.
To do so, select the range B6:H8. Choose Formulas, Defined Names, Create from Selection, or press Ctrl+Shift+F3. In the Create Names dialog, make sure that only Left column is selected. Then choose OK.
The item number in cell D6 specifies the number of items in the list in column D. Here’s the formula that returns this number in the cell shown:
D6: =COUNTA(D$8:D$15)
(The COUNTA function, as you might member, counts both numbers and text, but not empty cells.)
After you enter the formula, copy it to the right as shown.
Begin the List Boxes
Enter and format the text and formatting in rows two and three. Then use the Create Name dialog to assign Product and State as names for the two yellow cells to their right.
(The cells are yellow, by the way, to provide a visual clue that these cells contain settings that can be changed.)
In a little while, you’ll add dropdown list boxes to those two cells. But first, you need to set up two dynamic range names.
Create the First Dynamic Range Name
The list boxes rely on two dynamic range names that are rather long. To explain the first name, I’m going to chop it into two sections, and then combine those sections into one long formula.
For the first section, we need to create an OFFSET formula that returns a reference to the cell in the table that contains the label entered in the yellow Product cell. To do that, we use an INDEX-MATCH formula.
The syntax formulas for these two functions are:
=INDEX(reference,row_num,column_num,area_num)
=MATCH(lookup_value,lookup_array,match_type)
So in any empty cell, enter this formula:
=INDEX(Items,1,MATCH(Product,Products,0))
Here’s what this formula tells Excel to do:
Starting with the entire Items range, return a reference to the cell found in row 1 of the range and in the column number specified by the MATCH formula.
To find that column number, use MATCH to look for the specified Product in the Products list. Because MATCH’s third argument is zero, the products can be listed in any order and an exact match is required.
In this example again…
..the INDEX-MATCH formula returns a reference to cell D6, which contains the value 3.
Now we use an OFFSET-MATCH formula, where the syntax for OFFSET is:
=OFFSET(reference,rows,cols,height,width)
The preliminary formula is:
=OFFSET(TopRow,1,MATCH(Product,Products,0)-1,(3),1)
(Here, the “(3)” near the end of this formula is the value returned by the INDEX-MATCH formula we just looked at.)
Here’s what this formula tells Excel to do:
Return a reference that begins one row below the gray TopRow range. Move the number of columns to the right specified by the MATCH function, less one column. (Here, for example, “Hats” is in the second column, so move just one column to the right from cell C8.) Return a reference that is 3 rows high and 1 column wide.
So when we combine both sections into one long formula, we get:
=OFFSET(TopRow,1,MATCH(Product,Products,0)-1,
INDEX(Items,1,MATCH(Product,Products,0)),1)
To test this formula, first enter it as one long formula in any cell, which should return a #VALUE! error. Now select the formula in your formula bar; press Ctrl+c to copy it; press Esc to return to the ready mode; press the F5 key to launch the GoTo dialog; paste the formula into the Reference box; and then press Enter. After you do so, Excel should select the area with the three states below the Hats label in the table.
Once this test works, define the name CurrentStates. To do so, choose Formulas, Defined Names, Define Name (or choose Ctrl+Alt+F3) to launch the New Name dialog. Type CurrentStates into the Name box; paste your copied formula into the Refers-to box; and then press Enter.
To test that the name is working as expected, press the F5 key again and then enter CurrentStates into the Reference box. After you do so, Excel should select the three states again.
Create the Second Dynamic Range Name
The second dynamic range name returns the list of products from the Products row, but excludes the beginning and ending gray cells from the list. To create the name, first copy this formula to your clipboard:
=OFFSET(Products,0,1,1,COLUMNS(Products)-2)
Then press Ctrl+Alt+F3 to launch the New Name dialog; type CurrentProducts into the Name box; paste the formula into the Refers-to box; and then press Enter.
Be sure to test this name by using the GoTo dialog box.
Set Up the Dropdown List Boxes
To set up the dropdown list boxes, first select cell E2. Choose Data, Data Tools, Data Validation. In theSettings tab of the Data Validation dialog, choose List in the Allow dropdown list box, and in the Source box, type…
=CurrentProducts
…and then choose OK.
Similarly, select cell E3, launch the Data Validation dialog again, and type…
=CurrentStates
…in the Source box, and then choose OK.
Set Up the Conditional Formatting
As you remember, we need cell E3, the State cell, to turn red if we choose a product with no sales in the specified state. To do that, we use this conditional formatting formula:
=ISNA(MATCH(E3,CurrentStates,0))
This formula returns TRUE if the MATCH function can’t find the contents of cell E3 in the CurrentStates range. And when we use a formula to set up a conditional format, the format is turned on when the formula returns TRUE.
So, to begin the conditional format, copy the “ISNA” formula above.
Select cell E3, making sure that the address of your selected cell is the same address as in the formula above. Choose Home, Styles, Conditional Formatting, New Rule. In the New Formatting Rule dialog, choose Use a formula to decide which cells to format. In the dialog’s first edit box, which is labeled Format values where this formula is true, paste the formula that you copied from above.
Next, click on the Format button. In the Fill tab of the Format Cells dialog, choose the bright red square in the bottom row of the palette. Then choose OK, and then choose OK again.
Your cascading list boxes now should work as I described at the beginning of this article. You can select any product in the dropdown list box in cell E2. And then you can choose any state from the dropdown list box in cell E3. If you choose a product that isn’t sold in the current state, cell E3 should turn bright red. (For testing purposes, notice that Montana appears in the list for each product.)
Adapt the Example to Your Own Needs
To convert this setup to your own requirements, replace the product names with your own list of items, inserting as many columns as you need between columns F and G in the List Maintenance table. And you’ll probably want to change the word “Products” to a word that better describes your own list of items.
Second, replace the state names with the item names that you require for your secondary lists. Insert as many rows in the table as you need for additional items. And you’ll probably want to replace the text “States” with a word that better describes your lists of secondary items.
Finally, to change the range names that include Product or State in their names, first choose Formulas, Defined Names, Name Manager. Select a name that you want to change; choose Edit; change the name in the Edit dialog; and then choose OK.
