Create Dependent Drop-Down Lists in an Excel Quote Sheet
Question details
Create a quote sheet where the available options depend on the selected model.

- 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.
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.
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.
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.
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.
Select B2, open Data > Data Validation, and choose List under Allow. Set Source to =$D$2:$D$3 and click OK.
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.
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.

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. 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. 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. Add the model drop-down: Select B2, open Data > Data Validation, choose List, and enter =$D$2:$D$3 as the source.
- 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.

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.




