Function Problems

Create Dependent Drop-Down Lists in Excel from Another Sheet

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Create dependent coating drop-down lists from values stored in one column on another worksheet.

Create Dependent Drop-Down Lists in Excel from Another Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the layer selector

On the Input sheet, select E2 and choose Data > Data Validation. Set Allow to List and enter Base,Middle,Top in Source.

2
Filter the matching coatings

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.

3
Name the spilled range

Choose Formulas > Name Manager > New. Name the range CoatingOptions and set Refers to to =List!$D$2#.

4
Create the dependent list

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.

Filter coating choices with a spilled range in Excel
Check the source range: Expand the row 100 limits in the FILTER formula if your data extends farther. If you change the layer in E2, reselect the coating in F2; Excel does not automatically clear an earlier choice.
Work with spreadsheets in WPS

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. 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. 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. 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. 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.
Create layer-specific coating lists with named rangesOpen and save Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data validation toolsWork with a free, lightweight office suite
microsoft office alternative - wps office

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.