Home > Validation > Dependent > Dynamic Dependent Drop Down - Dynamic ArraysLimit choices in one Excel drop down list, based on the value in another cell. This example uses dynamic arrays and spilling to create the lists used in the drop downs. Select a region name, and then select an employee from that region. NOTE: Dynamic arrays are available in Microsoft Excel 365 plans. In versions of Excel without dynamic arrays, see these other ways to set up dependent drop down lists. |
Dependent Drop Down ListsThis video shows how to set up dependent drop down lists, based on dynamic arrays that have the items for each list. Select a region name from the first drop down, and the dependent drop down shows only the employees who work in that region There are written instructions below the video, and the sample file is in the download section at the bottom of the page. |
Worksheet SetupOn the Data Entry spreadsheet, there is a list with region and employee names. This list was formatted as an Excel Table. If names are added or removed, the list size will adjust automatically.
In column F and column G, data entry cells are set up at the top of the sheet, where you'll choose a region, and then you'll select an employee from that region. In the screen shot below, just the headings, fill colour and borders have been added to the data entry cells After everything is set up, those cells will have drop down lists, as you can see in this animated screen shot. |
Dynamic Unique ListWe want to create a drop down list of region names in cell F2. To get those region names, follow these steps to create a dynamic list of the unique region names. This will be the source range for the drop-down list.
NOTE: When you create a dynamic array, try to leave enough empty rows below the formula cell, and empty columns to the right (if needed). If there's not enough room, Excel will show a #SPILL error, instead of the list |
Sort the ListIn some drop down list, the items will be easier to find if they're sorted alphabetically. Follow these steps to sort the list of region names.
|
Make the First Drop DownNext, follow these steps to make a dropdown list of region names, in cell F2
|
Test the Drop Down ListTo test the drop down list of Region names:
|
Create an Employee ListIn cell G2, we want a dynamic drop down list of employees. Instead of showing all the names, we'd only like to see the employees from the region that was selected in cell F2. To create this dependent drop down list, we'll build another dynamic array formula, using the FILTER function.
Follow these steps to create the formula:
To test the dynamic list, select a different region name in cell F2. The list of employee names changes automatically. Here's the list after the East region was selected, with only 2 employee names showing. |
Sort the Employee NamesJust like we did with the region name list, we'll use the SORT function in this list, to put the employee names in alphabetical order. Because this list has 2 columns, we'll type a 2 as the second argument, to tell Excel to sort by the 2nd column. Follow these steps to sort the employee names.
|
Dependent Drop Down ListNext, we'll use data validation to create a drop down list of employee names. This is a dependent drop down list -- its contents depend on what region was selected in cell F2. Because the employee names are in a 2-column list, we'll use the INDEX function to return the names from the 2nd column. In the INDEX function,
Drop Down List of EmployeesFollow these steps to make a drop down list of employee names, in cell G2
|
Test the Drop Down ListTo test the drop down list of employee names:
|
Fix the ErrorWhen cell F2 is empty, cell F2 shows a #CALC! error, because there's no region for the filter. If you'd rather not see that error, use the IFERROR function to show an empty cell, or show a message. For example, change the formula in cell F7 to the following: =IFERROR(SORT(FILTER(A2:B11, A2:A11=F2),2), "Select a region") The message, "Select a region", will show, instead of the #CALC! error. |
Download the Sample File
NOTE: Dynamic arrays are available in Microsoft Excel 365 |
More Tutorials |
Last updated: March 23, 2023 3:49 PM