Create a Dependent Pricing Drop-Down List in Excel
Question details
Create a pricing drop-down list whose options depend on the selected pricing structure.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user selects a pricing structure, such as MSRP or distributor quantity, and then chooses an associated pricing option.
- Observed behavior
- The second drop-down should show only the pricing options for the selected structure.
Prepare a separate column of pricing options for each structure. Choose category names that are valid Excel defined names, such as MSRP and Distributor_Qty, and use those same names in the first drop-down.
Use named ranges and INDIRECT for dependent pricing lists
Use this method when each pricing structure has its own list of options.
On a worksheet, enter MSRP and Distributor_Qty in E2:E3. Enter the MSRP options in F2:F4 and the distributor options in G2:G4, adjusting the ranges to fit your lists.
Select F2:F4, click the Name Box to the left of the formula bar, type MSRP, and press Enter. Select G2:G4 and name it Distributor_Qty in the same way.
Select A2, open Data > Data Validation, choose List, and enter =$E$2:$E$3 in the Source box. Click OK.
Select B2, open Data > Data Validation, choose List, and enter =INDIRECT($A2) in the Source box. Click OK, then select a structure in A2 and open the drop-down in B2 to test its options.
Copy A2:B2, select the corresponding cells below, and use Home > Paste > Paste Special > Validation. Each dependent list will refer to the structure in column A of its own row.
Build dependent pricing lists in WPS Spreadsheets
Use named ranges, Data Validation, and INDIRECT in WPS Spreadsheets to show pricing options for the selected structure.
- 1. Enter pricing lists: In WPS Spreadsheets, enter MSRP and Distributor_Qty in E2:E3, then place their respective pricing options in F2:F4 and G2:G4.
- 2. Define the ranges: Select F2:F4 and type MSRP in the Name Box beside the formula bar. Press Enter, then name G2:G4 Distributor_Qty.
- 3. Add the structure list: Select A2, open Data > Data Validation, choose List, and enter =$E$2:$E$3 as the source.
- 4. Add the dependent list: Select B2, open Data > Data Validation, choose List, and enter =INDIRECT($A2) as the source. Select a structure in A2 and test the list in B2.

Frequently Asked Questions
Why does the dependent drop-down show an error or no options?
Check that the value selected in A2 matches a defined range name. Open Formulas > Name Manager to inspect the names and their cell references, then check that B2 uses =INDIRECT($A2) as its Data Validation source.
Can a pricing structure name contain spaces?
Excel defined names cannot contain spaces. Use a name such as Distributor_Qty in both the category list and Name Manager, or use a formula that converts spaces to underscores.
Does changing the pricing structure clear the previous selection?
No. If you change the structure in column A, clear the existing value in column B and choose a new option from its drop-down.
How do I use the drop-downs on multiple rows?
Copy the validated cells in A2:B2 and use Home > Paste > Paste Special > Validation on the rows below. The formula =INDIRECT($A2) adjusts its row reference for each destination row.




