Function Problems

Create a Dependent Pricing Drop-Down List in Excel

Anum MushtaqAnum Mushtaq Nov 20, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

Use named ranges and INDIRECT for dependent pricing lists

Use this method when each pricing structure has its own list of options.

1
Enter the source lists

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.

2
Name each pricing range

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.

3
Create the structure drop-down

Select A2, open Data > Data Validation, choose List, and enter =$E$2:$E$3 in the Source box. Click OK.

4
Create the dependent drop-down

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.

5
Apply the lists to other rows

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.

Keep names consistent: Each value in the structure list must match a defined range name. If you change the structure after choosing a price, clear the previous price and select a new one.
WPS Spreadsheets

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. 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. 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. 3. Add the structure list: Select A2, open Data > Data Validation, choose List, and enter =$E$2:$E$3 as the source.
  4. 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.
Create dependent pricing drop-down lists with INDIRECTOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.