Drop Down menu in MS Excel

Create a three-level dependent drop-down by storing the Belt Manager, Business Manager, and Fr. ID relationships in one Excel table, then using dynamic-array helper lists for each Data Validation source.

1. Create the relationship table

On a sheet named Data, arrange the records like this:

Each row must represent one valid relationship.

1. Select the data.
2. Press Ctrl+T.
3. Confirm My table has headers.
4. On Table Design, change the table name to ManagerTable.

Using an Excel table allows the source data to expand when records are added.^1^

Assume the selection cells on another sheet named Entry are:

• A2: Belt Manager
• B2: Business Manager
• C2: Fr. ID

2. Build the filtered helper lists

Create a sheet named Lists and enter these formulas:

Lists!A2 — unique Belt Managers

=SORT(UNIQUE(ManagerTable[Belt Manager]))

Lists!B2 — Business Managers belonging to the selected Belt Manager

=SORT(UNIQUE(FILTER(ManagerTable[Business Manager],
 ManagerTable[Belt Manager]=Entry!$A$2,"")))

Lists!C2 — Fr. IDs belonging to both selected managers

=SORT(UNIQUE(FILTER(ManagerTable[Fr. ID],
 (ManagerTable[Belt Manager]=Entry!$A$2)*
 (ManagerTable[Business Manager]=Entry!$B$2),"")))

UNIQUE removes duplicates and returns a dynamic array.^2^ The results automatically spill into the cells below; appending # to the first cell refers to the complete spill range even when its size changes.^3^

Keep the cells underneath A2, B2, and C2 empty, or Excel may return #SPILL!.

3. Name the three dynamic lists

Because the validation lists are on another worksheet, create workbook-level names:

1. Select Formulas > Name Manager > New.
2. Create these names:

Named ranges can be used as the formula source for list validation.^4^

4. Apply Data Validation

For each cell, select Data > Data Validation, set Allow to List, and ensure In-cell dropdown is selected.^5^

Configure:

The expected result is:

1. Select a Belt Manager in A2.
2. Open B2; only Business Managers associated with that Belt Manager appear.
3. Select a Business Manager.
4. Open C2; only matching Fr. IDs appear.

If an upstream choice is changed, clear the old downstream selections in B2 and C2 before choosing again; formulas update the available lists but do not automatically erase an earlier cell value.

This dynamic-array method applies to Microsoft 365 on Windows. The UNIQUE documentation also lists Excel 2021 and Excel 2024, but older versions without dynamic-array functions require a different helper-range or VBA approach.^2^

References

1. [Windows](https://support.microsoft.com/excel/get-started/create-a-drop-down-list)
2. UNIQUE function
3. Spilled range operator | Microsoft Support
4. Validation.Add method (Excel)
5. Apply data validation to cells