Dependent drop down list for multiple rows using Excel dynamic arrays (2024)

Source data

For starters, let's get some source data to work on. In our main table on the left, we want to have two picklists in each row, so that selecting a Dept. in the first list only displays Managers for that selected department in the second list. If you change the selection in the main list, the names in the dependent drop-down will update accordingly.

Step 1. Structure source data

The source data for drop-down lists often come from different sources and are organized differently. So, our first step is to structure the original data according to our needs. For this, we are going to create some sort of preparation table that will list all different department names in the header row, and under each dept. name there will be a list of employees working in that specific department. To automate the work and prevent human errors, we will be using the following formulas.

To get the departments, enter this formula in H2.

=TRANSPOSE(SORT(UNIQUE(E3:E15)))

Here, the UNIQUE function extracts all the different departments from E3:E15. The SORT function arranges the results in alphabetical order so that the items of your main list will be sorted from A to Z (if you don't want that, you can remove SORT from the formula, and the dept. names will appear in the same order as in your source table). Finally, TRANSPOSE changes the output orientation from vertical to horizontal.

Please notice that the formula needs to be entered just in one cell, and the results spill into neighboring cells automatically (this feature is called a spill range).

This way, we've got the items for our main drop-down list:

To pull the manager names, the formula in H3 is:

=SORT(FILTER($F$3:$F$15, $E$3:$E$15=H$2))

Here, we utilize the FILTER function to filter the employees that belong to a particular department ($E$3:$E$15=H$2). The SORT function arranges the names in alphabetical order (if you wish to preserve the existing order, remove it from the formula).

Like the previous formula, this one is also dynamic, and the whole spill rage is filled with the results at once. All you need to do is to drag the formula to the right to get a list of managers for each dept.

And this gives us the items for the dependent drop-down list:

Step 2. Make a primary drop-down list

With the source data properly arranged, create the first drop-down list in the usual way with the help of Excel Data Validation:

1. Select the topmost cell in which you want the primary dropdown list to appear (B3 in our case).
2. On the Data tab, in the Data Tools group, click Data Validation.
3. On the Settings tab of the Data Validation dialog box, configure the rule:
   • Under Allow, select List.
   • In the Source box, enter the spill range reference pointing to the departments names in the preparation table: =$H$2#

     This syntax (cell address followed by a hash tag) refers to the entire spill range no matter how many cells it actually contains.

   • Click OK to close the dialog.

The primary drop-down list for the first cell is done:

Step 3. Create a dependent drop-down list

Technically, setting up a successive picklist seems pretty easy - you just need to create one more data validation rule for another column. However, there's a tricky part - the formula for the Source box. This time, you cannot use a normal spill range reference because the second drop-down list needs to take into account the selection in the first drop-down. To solve this need, we'll be referring to a relevant spill range indirectly with this formula:

INDIRECT(ADDRESS(row_num, COLUMN(preceding_col) + MATCH(dropdown_cell, dropdown_spill_range, 0), 4) & "#")

Where:

• row_num - the number of the row in the preparation table in which the formula returning the items for the dependent dropdown resides. Our formula is in row 3 (H3:K3), so it's 3. Instead of hardcoding the row number, you can use the ROW() function, but we won't complicate things more than they already are.
• preceding_col - a cell reference to the column preceding the leftmost column of the preparation table. In this example, the preparation table begins in H2. The preceding column is G, so we use COLUMN(G1). Here, only the column coordinate matters, the row number can be any.
• dropdown_cell - is the address of the topmost cell containing the primary drop down list, B3 in our case. Please be sure to use a relative cell reference without the $ sign, so that the formula adjusts correctly for each row where you'll copy your drop-down.
• dropdown_spill_range - the reference to the spill range in the preparation table containing the items for the primary drop down list. In our example, it's the departments returned by the dynamic array formula in H2, so we use this spill range reference: $H$2#

Putting all the above pieces together, we get the following formula for the dependent drop down validation rule:

=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & "#")

Now, simply select the topmost cell for the secondary drop-down list (C3), go to the Data tab > Data Validation, and configure the rule like this:

That's it! The dependent drop-down list is good to go:

How this formula works:

From my experience, many of our blog visitors are eager to know not only how to apply this or that formula but also understand its internal logic. For such curious and thoughtful users, I'm providing the detailed break-down of the formula:

=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & "#")

On a high level, we utilize the INDIRECT function to "dynamically" refer to the spill range of managers corresponding to the department selected in the primary dropdown.

Suppose you picked Planning from the drop-down in B3. The employees of the Planning dept. are listed in column J beginning in J3. That means, we need to somehow supply the J3# reference to INDIRECT, and here's how we do it:

To get the column letter, the following 3 functions are used together:

ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4)

The COLUMN function returns the column number of G1, which is 7.

The MATCH function searches for the B3 value in list of departments beginning in H2 and return its relative position, which is 3 (Analysis, Design, Planning, …).

Adding up these two numbers gives us 10, so the ADDRESS function takes this form:

ADDRESS(3, 10, 4)

Where 3 is the row number, 10 is the column number and 4 is the relative cell reference type. The result is the "J3" reference that goes directly to INDIRECT :) and reduces our tricky formula to an easy understandable one:

INDIRECT("J3"&"#")

Concatenating a cell reference with a hash tag produces a spill range reference, so that INDIRECT returns an entire array rather than a single cell value. Done!

The beauty of this formula is that it works perfectly for a single-letter, two-letter and even tree-letter column (thank you Jonathan for pointing us in the right direction!).

Step 4. Copy drop down lists across multiple rows

This is the easiest part. For the picklists to appear in multiple cells, you can simply copy them like any other cell content by using the Copy / Paste feature or by dragging through the adjacent cells. For practice, we will try both techniques.

To copy the primary drop down list, select the cell containing it (B3) and drag the fill handle (a small square at the lower right-hand corner of the cell) through as many cells as needed:

To copy the dependent drop down list, let's do a regular copy/pasting:

1. Select the cell with the secondary drop-down (C3) and press Ctrl + C to copy it.
2. Select all other cells where you want the dependent list to appear (C4:C12) and press Ctrl + V to paste the copied contents.

Now, both picklists appear in every row letting you select a department and manager for each project.

Please keep in mind that the above methods copy all the contents of a cell including Data Validation and the current selection. So, they are best to be used when no item has been selected in the list yet.

If you've already selected the items, then you can use the Paste Special feature to copy the Data Validation rule.

Result: multiple rows dependent drop down list

Though our dynamic arrays solution is not a one-click setup, it's a way faster than it looks and definitely much faster than the old-fashioned approach with named ranges. Anyway, the result is worth the effort, agree?

Here it is - a multi-level drop down for multiple rows where selecting an item from the first list determines which items will appear in a secondary drop-down.

How to make multiple drop-down list expandable

If more data is likely to be added to your source table in the future and you wish to have new entries included in the drop-down lists automatically, then you will have to use a bit more complex versions of the formulas for the preparation table. There are 2 possible approaches here, a regular range and a full-fledged Excel table, each having its own caveats.

Practice workbook for download

Multiple rows dependent drop-down (.xlsx file)