Formula Errors

How to Fix Excel Dependent Drop-Down Formulas Not Copying Correctly

John Wilson Oct 10, 2026 868 views

Question details

A dependent drop-down list using XLOOKUP fails to update row references when copied down, causing all rows to refer back to the initial category cell.

How to Fix Excel Dependent Drop-Down Formulas Not Copying Correctly

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic dependent drop-down lists across multiple rows using Data Validation and XLOOKUP functions.
Observed behavior: When copying the drop-down list to lower rows, the formula continues referencing the initial row's category cell (e.g., D2) instead of automatically updating to the corresponding row (e.g., D3 or D4).

Before you start

Ensure your source data categories are accurately organized and verify that your XLOOKUP formula successfully retrieves data in the first row before modifying its references.

Solution 1Recommended

Convert Absolute References to Relative References in Data Validation

Use this solution to remove the fixed row restrictions in your formula so the drop-down list updates dynamically for each new row.

When setting up Data Validation, Excel often defaults to using absolute references (e.g., $D$2). The dollar signs lock the reference to that specific cell. By removing the dollar signs to create a relative reference (e.g., D2), the formula will adapt to the corresponding row when copied down.

Select the Initial Drop-Down Cell

Click on the cell in the first row that contains your working dependent drop-down list (for example, cell E2).

Open Data Validation Settings

Navigate to the 'Data' tab on the top ribbon and click on 'Data Validation'.

Modify the XLOOKUP Formula

In the Data Validation dialog box under the 'Source' field, locate the reference to your category cell (e.g., $D$2). Remove the dollar signs to make it a relative reference (e.g., D2).

Apply and Copy Down

Click 'OK' to save the changes. Then, select the cell, click and hold the fill handle at the bottom right corner, and drag it down to apply the updated drop-down list to the remaining rows.

Convert Absolute References to Relative References in Data Validation

Verify Results: After copying down, check the Data Validation settings of a cell in a lower row (e.g., E4) to ensure the formula now references its corresponding category cell (e.g., D4).

Simplify Data Management with WPS Spreadsheet

Create Dynamic Dependent Drop-Downs in WPS Office

WPS Spreadsheet provides robust Data Validation and full XLOOKUP support, allowing you to easily configure and duplicate dynamic drop-down lists without formatting errors.

1. Open your Spreadsheet: Launch WPS Spreadsheet and open the document where you want to add the dependent drop-down lists.
2. Access Data Validation: Select the target cell, go to the 'Data' tab, and click on the 'Validation' button.
3. Enter the Relative Formula: Select 'List' from the Allow menu and enter your XLOOKUP formula using relative references (e.g., =XLOOKUP(D2,...)).
4. Drag to Apply: Click OK, then drag the cell's fill handle downward to smoothly duplicate the dependent drop-down logic to all required rows.

Fully compatible with Microsoft Excel formulas and formats (.xlsx)Intuitive Data Validation interface for faster setupsFree, lightweight, and fast alternative for heavy spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas not update when I drag them down?

This generally occurs because the formula uses absolute references, indicated by dollar signs (e.g., $D$2). Absolute references lock the cell location, preventing it from changing when copied. Removing the dollar signs converts it to a relative reference, allowing the formula to adjust automatically per row.

Can I use the INDIRECT function instead of XLOOKUP for dependent drop-downs?

Yes, INDIRECT is a traditional and highly reliable method for creating dependent drop-down lists. By using Named Ranges that match your category names, you can set the Data Validation source to =INDIRECT(D2), which achieves a similar dynamic result.

How do I completely remove a drop-down list from a cell?

To remove a drop-down list, select the cell or range of cells containing it. Navigate to the 'Data' tab, click 'Data Validation', and in the dialog box, click the 'Clear All' button at the bottom left, then click 'OK'.

Excel & Data Problems