Dependent drop down list for multiple rows using Excel dynamic arrays

This tutorial demonstrates how to create expandable, multi-row dependent dropdown lists in Excel using dynamic arrays. This method significantly simplifies the process compared to traditional approaches. The solution is only compatible with Excel for Microsoft 365 and Excel 2021 due to its reliance on dynamic array features.

Data Preparation:

The first step involves structuring your source data. Assume you have a table with "Dept." and "Manager" columns. A preparation table is created to organize this data for the dropdown lists. This table lists unique department names horizontally (using =TRANSPOSE(SORT(UNIQUE(E3:E15))) for departments), with each column below containing the corresponding managers (using =SORT(FILTER($F$3:$F$15, $E$3:$E$15=H$2)) for managers). This automatically expands to include new entries. Consider placing this preparation table on a separate sheet for better organization and to avoid potential conflicts with other data.

Creating the Dropdowns:

1. Primary Dropdown: Use Data Validation (Data > Data Validation) in the first cell of your main table (e.g., B3). Set "Allow" to "List," and for "Source," use =$H$2# (referencing the spill range of department names in the preparation table).

2. Dependent Dropdown: In the next cell (e.g., C3), use Data Validation again. Set "Allow" to "List," and for "Source," use the following formula:

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

   This formula dynamically references the correct manager list based on the department selected in the primary dropdown. If your data is on a different sheet (e.g., "Sheet1"), adjust the formula accordingly:

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

Expanding the Dropdowns:

To apply the dropdowns to multiple rows, simply copy the cells containing the Data Validation rules (B3 and C3) down the column. The formulas will automatically adjust for each row.

Expandable Data:

For automatic expansion to handle new data entries, consider these approaches:

• Excel Table: Convert your source data into an Excel Table. Modify the formulas in the preparation table to use structured references, ensuring absolute references for columns. The table's automatic expansion will update the dropdown lists.
• Improved Range Formulas: Use larger ranges in your formulas (e.g., E3:E30) and incorporate FILTER to exclude blank cells. This allows for adding new data within the defined range.

This method provides a robust and efficient solution for creating expandable, multi-row dependent dropdown lists in Excel, streamlining data entry and reducing manual effort. Remember to download the practice workbook for a hands-on experience.

The above is the detailed content of Dependent drop down list for multiple rows using Excel dynamic arrays. For more information, please follow other related articles on the PHP Chinese website!