Formula Errors

Fix Excel Formulas Skipping Inserted Rows When Referencing Another Sheet

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs a formula that references another worksheet without shifting or skipping rows when new rows are inserted in the source data. They also want to handle blank cells properly and avoid #REF! errors when source rows are deleted.

Product: Excel
Device & OS: not provided
Scenario: Referencing data from another worksheet while managing inserted/deleted rows and retaining blank cells.
Observed behavior: Standard cell references shift or skip when a source row is inserted, blank cells show as zeros, and deleting rows results in #REF! errors.

Before you start

Ensure you know the exact name of the source worksheet (e.g., 'Data') and the specific column range you want to mirror before applying these dynamic position-based formulas.

Solution 1Recommended

Use a Position-Based Formula with INDEX and LET

This method ties the reference to the row and column numbers rather than specific cell addresses, making it immune to inserted or deleted rows.

By using the INDEX, ROW, and COLUMN functions inside a LET function, you can dynamically retrieve data based on its physical location in the sheet.

This approach also ensures that blank source cells are returned as empty strings instead of zeros, and deleted source rows won't trigger broken reference errors.

Select the target cell

Click on the cell in your destination worksheet where you want the linked data to appear (for example, cell A2).

Input the position-based formula

Type the formula =LET(d,INDEX(Data!$A:$Z,ROW(),COLUMN()),IF(d="","",d)) into the formula bar. Replace 'Data!$A:$Z' with your actual source sheet name and target column range.

Apply across rows and columns

Press Enter, then drag the fill handle at the bottom-right corner of the cell across and down to copy this formula to the rest of your intended range.

No More #REF! Errors: Because this formula doesn't lock onto specific cell IDs, deleting rows in the source sheet will no longer break your references.

Solution 2

Use a Dynamic Array Formula for Spill Ranges

If you are using a modern version of Excel or WPS Office that supports dynamic arrays, you can use a single formula to populate an entire range.

Manage Complex Formulas with WPS Office

Easily Handle Cross-Sheet References in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like LET, INDEX, ROW, and dynamic arrays. You can easily link data between worksheets without worrying about shifting rows or #REF! errors using our highly compatible and lightweight software.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cross-sheet data.
2. Enter the INDEX formula: Select your target cell and input =LET(d,INDEX(Data!$A:$Z,ROW(),COLUMN()),IF(d="","",d)).
3. Fill the range: Use the smart fill handle to drag the formula across your desired rows and columns seamlessly.

100% compatible with Microsoft Excel formulas (.xlsx)Supports advanced dynamic array functions and spill rangesBuilt-in error checking for broken references and formula issuesLightweight, fast, and completely free to use

Frequently Asked Questions

Why does my formula skip rows when I insert new data?

Standard direct cell references (like =Data!A2) automatically shift to adjust when rows are inserted above them. This is an intended feature to keep formulas tied to specific data points, but it causes row skipping when you want to mirror a static row layout.

How do I stop blank cells from showing as zero in Excel?

You can wrap your cell reference in an IF statement to check for blanks. For example, =IF(Data!A2="","",Data!A2) evaluates the cell and returns an empty string if the source is blank, preventing it from defaulting to zero.

What causes the #REF! error in cross-sheet formulas?

A #REF! error occurs when a cell that your formula directly references is permanently deleted. Using indirect or position-based formulas like INDEX prevents this because the formula looks at a coordinate rather than a specific cell object.

Can I use the LET function in older versions of Excel?

The LET function is only available in newer versions (like Office 365, Excel 2021, and the latest WPS Office). For older versions, you must repeat the INDEX expression inside the IF statement, such as =IF(INDEX(Data!$A:$Z,ROW(),COLUMN())="","",INDEX(Data!$A:$Z,ROW(),COLUMN())).

Excel & Data Problems