Create Dependent Drop-Down Lists in Excel from Another Sheet
Question details
Create dependent coating drop-down lists from values stored in one column on another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to select a base, middle, or top layer and then choose a matching coating from a list on another sheet.
- Observed behavior
- The coating list should show only values associated with the selected layer.
On a sheet named List, place the layer category in column A and the corresponding coating in column B, starting in row 2. Use consistent category labels such as Base, Middle, and Top, and create an Input sheet for the drop-downs.
Filter coating choices with a spilled range in Excel
Use this method in Microsoft 365 when you want the coating options to update from a categorized source table.
On the Input sheet, select E2 and choose Data > Data Validation. Set Allow to List and enter Base,Middle,Top in Source.
On the List sheet, enter =FILTER($B$2:$B$100,($A$2:$A$100=Input!$E$2)*($B$2:$B$100<>""),"") in D2. Leave the cells below D2 empty so the results can spill.
Choose Formulas > Name Manager > New. Name the range CoatingOptions and set Refers to to =List!$D$2#.
On Input, select F2 and choose Data > Data Validation. Set Allow to List and enter =CoatingOptions in Source. Select a layer in E2, then open the drop-down in F2.

Use named ranges and INDIRECT for grouped coating values
Use named ranges when FILTER is unavailable and each layer's coating values can be grouped into a contiguous block.
Create dependent coating lists in WPS Spreadsheets
Use named ranges and data validation in WPS Spreadsheets to build a layer selector and a matching coating drop-down from another sheet.
- 1. Organize the source data: Open your workbook in WPS Spreadsheets. On the List sheet, place layer labels in column A and coatings in column B, then sort both columns together so each layer's coatings are grouped.
- 2. Name each coating block: Select each contiguous coating block in column B and use Formulas > Define Name to name it Base, Middle, or Top, matching the layer label.
- 3. Create the layer selector: On the Input sheet, select E2 and open Data > Data Validation. Choose a list and enter Base,Middle,Top as the source.
- 4. Create the coating selector: Select F2, open Data > Data Validation, choose a list, and enter =INDIRECT($E$2) as the source. Choose a layer in E2 and check the options in F2.

Frequently Asked Questions
Why is my filtered coating list not spilling?
Check the cells below List!D2 and clear anything blocking the results. Also make sure your Excel version supports FILTER and dynamic arrays.
Why does the INDIRECT drop-down show an error?
Open Formulas > Name Manager and check that each range name exactly matches the selected label in Input!E2. Simple labels such as Base, Middle, and Top work as range names.
Will a coating selection change automatically when I pick another layer?
No. After changing Input!E2, open the coating drop-down in F2 and select a valid option for the new layer.
How do I include new coatings in the drop-down?
For the FILTER method, add the category and coating within the formula's source rows or expand its references. For the named-range method, update the appropriate range in Formulas > Name Manager.




