Others

Create Dependent Drop-Down Lists in an Excel Quote Sheet

Ayan MasoodAyan Masood Nov 14, 2026 868 views

Question details

Create a quote sheet where the available options depend on the selected model.

Create Dependent Drop-Down Lists in an Excel Quote Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user selects a model in a quote sheet and then chooses an option associated with that model.
Observed behavior
The option drop-down should show only the choices associated with the selected model.
Before you start

Prepare a small sample with model names and the options for each model. For the named-range method below, use model names that are valid Excel range names, such as Sedan and SUV.

Solution 1Recommended

Create cascading lists with named ranges and INDIRECT

Use this method when each model has a separate list of options and its name can also be used as a range name.

The second drop-down uses INDIRECT to turn the selected model name into a reference to the matching named range.

1
Enter sample data

In a worksheet, enter Sedan in D2 and SUV in D3. Enter Standard and Sport in E2:E3, then Standard and Off-road in F2:F3.

2
Name each option range

Select E2:E3 and enter Sedan in the Name Box to the left of the formula bar. Select F2:F3 and name it SUV. Each range name must match its model value.

3
Create the model list

Select B2, open Data > Data Validation, and choose List under Allow. Set Source to =$D$2:$D$3 and click OK.

4
Create the dependent list

Select C2, open Data > Data Validation, and choose List under Allow. Set Source to =INDIRECT($B$2) and click OK. If Excel warns that the source evaluates to an error, select a model in B2 first.

5
Test both selections

Choose Sedan in B2 and open the drop-down in C2 to check for Standard and Sport. Clear C2, select SUV in B2, and confirm that C2 offers Standard and Off-road.

Create cascading lists with named ranges and INDIRECT
Clear the old option: Changing the model does not automatically clear a previously selected option. Clear C2 whenever you change B2, then select an option from the updated list.
Create cascading lists in WPS

Build a dependent quote-sheet list in WPS Spreadsheet

Use named ranges and data validation in WPS Spreadsheet to show options for the model selected in your quote sheet.

  1. 1. Enter the model and option lists: In WPS Spreadsheet, enter Sedan and SUV in D2:D3, their respective options in E2:E3 and F2:F3, and reserve B2 and C2 for the selections.
  2. 2. Name the option ranges: Select E2:E3 and use the Name Box beside the formula bar to name it Sedan. Name F2:F3 SUV.
  3. 3. Add the model drop-down: Select B2, open Data > Data Validation, choose List, and enter =$D$2:$D$3 as the source.
  4. 4. Add and test the option drop-down: Select a model in B2. Then select C2, open Data > Data Validation, choose List, and enter =INDIRECT($B$2) as the source. Open C2's drop-down to check the available options.
Create model-specific option lists with INDIRECTOpen and save Microsoft Excel XLSX workbooksUse familiar spreadsheet tools for quote sheets
microsoft office alternative - wps office

Frequently Asked Questions

Why does the dependent drop-down show an error?

Check that B2 contains a model and that its text matches a named range exactly. In Excel, use Formulas > Name Manager to inspect the range names and their references.

Can model names contain spaces?

Excel range names cannot contain spaces. Use valid range names such as Sedan_Plus and adjust the dependent list source to =INDIRECT(SUBSTITUTE($B$2," ","_")) if the model cell displays Sedan Plus.

Will changing the model automatically remove the old option?

No. Data validation updates the available choices but does not automatically clear the existing value. Clear the option cell and select a new value after changing the model.

How do I add another model and its options?

Add the model to the model list, enter its options in a separate range, and give that range the same valid name as the model. Update the model cell's Data Validation source to include the new model.