Use INDIRECT with Dynamic Excel References When Rows Move
Question details
The user needs a way to maintain dynamic cell references in a summary sheet using the INDIRECT formula.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a monthly payroll workbook containing separate monthly worksheets and a main summary sheet that aggregates the data using INDIRECT formulas.
- Observed behavior
- When new rows are added to the monthly worksheets, the totals table shifts, causing the static text references within the INDIRECT formulas on the summary sheet to pull incorrect data.
Ensure your workbook's layout is consistent across all monthly sheets, and familiarize yourself with the Name Manager tool, as you will need to create worksheet-scoped named ranges for this solution to work.
Use Worksheet-Scoped Named Ranges with INDIRECT and OFFSET
By defining a worksheet-scoped named range for your target totals cell, the reference will update automatically when rows or columns are inserted. Combining this with the OFFSET function allows you to retrieve relative data seamlessly.
The standard INDIRECT function uses static text strings (like "M21") which do not automatically update when rows or columns are shifted. To resolve this, we can use a defined name that adapts to changes in the sheet's structure.
When you wrap this named reference in an INDIRECT formula and use OFFSET, you can dynamically target the shifting cells without breaking the summary table formulas.
Navigate to the monthly data sheet (e.g., September 2020). Go to the Formulas tab, select Define Name, and create a name such as 'Summary'. Set its scope to the specific worksheet and refer it to the totals cell (e.g., ='September 2020'!$M$21).
In your summary sheet, ensure you have a column (like Column B) containing the exact sheet name strings formatted to match the named range, such as 'September 2020'!.
In your summary table (e.g., cell D2), enter the formula: =OFFSET(INDIRECT($B2&"Summary"),ROW()-ROW($D$2),COLUMN()-COLUMN($D$2)). This uses INDIRECT to resolve the dynamic named range and OFFSET to move relative to it.
Drag the fill handle to copy this formula across the remaining cells in the summary table. Because the Named Range updates dynamically on the source sheet, inserting rows will no longer break the calculation.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for advanced reference functions like INDIRECT and OFFSET, along with an intuitive Name Manager to keep your multi-sheet workbooks organized and dynamic.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet payroll workbook.
- 2. Open Name Manager: Navigate to the Formulas tab and click on Name Manager to easily create and verify your worksheet-scoped named ranges.
- 3. Apply advanced functions: Type =OFFSET(INDIRECT(...)) into your summary sheet. WPS Spreadsheet's smart formula autocomplete will guide you through the syntax.
- 4. Save effortlessly: Save your document in .xlsx format to retain perfect formatting and formula functionality across different platforms.
Frequently Asked Questions
Why does the INDIRECT function break when I insert a row?
The INDIRECT function evaluates raw text strings (like "M21") as cell references. Because the text string remains static, it does not automatically increment to "M22" when you insert a row, causing the formula to pull data from the wrong cell.
What is a worksheet-scoped named range?
A worksheet-scoped named range is a custom label defined for a specific cell or range that applies only to an individual sheet, rather than the whole workbook. This allows you to use the exact same name (like 'Summary') on multiple different monthly tabs without conflict.
How does OFFSET work together with INDIRECT?
INDIRECT establishes the starting anchor point by dynamically reading the named range. OFFSET is then applied to that anchor point to move a specific number of rows or columns away, allowing you to extract entire tables of data relative to a single dynamic starting cell.
