Limit the choices in Excel drop down lists, based on the selections made in other drop downs. This example has 3 levels of dependent drop downs - 1) select a region, 2) pick a sales rep from that region, 3) choose a customer for that region and sales rep.
To get started with a simpler example, go to the Dependent Drop Down - Dynamic Arrays page.
NOTE: This example uses dynamic array formulas and spilling to create the lists used in the drop downs. Dynamic arrays are available in Microsoft 365 plans. For earlier versions of Excel, without dynamic arrays, use other techniques for setting up dependent drop down lists.
In this example, there is a formatted Excel Table on the Sales sheet, where weekly sales calls are entered. There are data validation drop down lists in the Region, Sales Rep (Rep) and Customer (Cust) columns.
NOTE: This example uses dynamic array formulas and spilling to create the lists used in the drop downs. Dynamic arrays are available in Microsoft 365 plans. For earlier versions of Excel, without dynamic arrays, use other techniques for setting up dependent drop down lists.
On the CustInfo sheet, there is another named Excel table, with a list of customer names, the region where they're located, and the sales rep who's assigned to their account.
The table is named tblCust, and it provides the items for the drop down lists in the Weekly Sales Calls table.
The table columns are named ranges, and you can see those names in the Name Manager:
Those names will be used in the workbook formulas
In the Region column of the Sales Calls table, the drop down list shows a list of the region names, in alphabetical order, Central, East and West.
The region names in the drop down list come from a list on the Lists01 sheet.
There is a dynamic array formula in cell B3, and the results spill into the cells at the right, as far as needed. The blue border outlines the current spill area.
This is the dynamic array formula in cell B3 on the Lists01 sheet:
The formula uses 3 of Excel's dynamic array functions:
The region list cells have been named as RegList. You can see its formula in the Name Manager:
The number sign (#) at the end of the formula is a spill operator. The named range, RegList, will include all of the dynamic array formula spill cells.
To create a drop down list of region names in the Sales Calls table, I followed these steps:
In the Rep column of the Sales Calls table, the drop down list shows the sales rep names for the selected region.
These lists are on the Lists01 sheet. starting with this formula in cell B5:
The formula uses 3 of Excel's dynamic array functions, and IFERROR:
Unlike the RegList formula, this list does not automatically spill across the columns, to show the rep list for each of the regions.
To show the rep lists for the other regions, the formula from B5 is copied across to cell G5. That allows a few extra columns for rep lists, in case new regions are added later.
Next, another named range was created - RegRepList. Its formula returns the rep list for the region in the current row.
Here is the Refers To formula for the RegRepList name:
Here's how the OFFSET formula works, to return the correct rep list, when East is the selected region:
I followed these steps to make a drop down list of sales reps, for the region selected in the current row:
If East is selected in cell B4, the drop down in the Rep column shows Al and Dee, the sales reps from the East region.
In the Sales Calls table, the Customer drop down shows the customers for the selected region and sales rep. To create that list, there are dynamic array formulas on the Lists02 sheet.
The first formula, in cell B3, creates a unique list of the regions, and their sales reps:
=TRANSPOSE(UNIQUE(SORTBY(RegCol:RepCol, RegCol,1, RepCol,1)))
There's another set of dynamic array formulas in row 7, to list the customers for the region and rep names in rows 3 and 4.
This formula is in cell B7, and copied across to J7. Extra cells were included, in case more region/sales rep combinations are added later.
=IFERROR(SORT(FILTER(CustCol, (RegCol=B3)*(RepCol=B4))),"")
The final formula is in cell B5, and it combines the region and rep names, with an underscore character between them.
=TRANSPOSE(SORT(UNIQUE(RegCol&"_"&RepCol)))
That spill range is named RegRepList, and it is used to find the correct list for the Customer drop down.
Next, another named range was created - RegRepSelCust. Its formula returns the customer list for the region and sales rep in the current row.
Here is the Refers To formula for the RegRepCustList name:
Here's how the OFFSET formula works, to return the correct rep list, when East is the selected region, and Dee is the selected sales rep:
I followed these steps to make a drop down list of sales reps, for the region selected in the current row:
If East is selected in cell B4, and Dee is selected in cell C4, the drop down in the Customer column shows Dan, Fran and Mel, the customers from the East region, for sales rep, Dee.
Because some of the formulas have to be copied across manually, there are formula counts on the Lists01 and Lists02 sheet.
In cell I3, this formula counts the regions in the RegList spill range
In cell L1, this formula counts the cells with data, or an empty string, in row 5, and subtracts the count of data in cell A5.
In cell E1, this formula shows a message, based on the counts
In cell I3, this formula counts the regions in the RegRepList spill range
In cell L1, this formula counts the cells with data, or an empty string, in row 7, and subtracts the count of data in cell A7.
In cell E1, this formula shows a message, based on the counts
The message cells are linked to cells F1 and G1 on the Sales sheet.
These messages will alert you if more formulas are needed on either of the lists sheets.
NOTE: Dynamic arrays are available in Microsoft 365 plans.
Drop Down - Latest Items at Top
Dependent Drop Down Setup Choices
Dependent Drop Down Lists Video
Dependent Dropdowns from a Sorted List
Hide Previously Used Items in a Dropdown List
Use a Data Validation List from Another Workbook
Author: Debra Dalgleish
Last updated: August 14, 2022 9:21 PM