Forum Discussion
Inserting copied cells in one sheet without shifting cell references in another sheet
Instead of e.g.
='Master sheet'!B2
i've applied this formula
=INDIRECT(ADDRESS(ROW(B2),2,,,"Master sheet"))
in another sheet in cell M2.
I then filled the formula down in the other sheet from cell M2 to M30.
In the master sheet i've shifted 3 cells down from cell B10 and entered data from another source.
The INDIRECT(ADDRESS(... formula still returns the values from range B2:B30 from the master sheet which includes the data from the 3 inserted cells.
That is an essential property of single-cell relative referencing and a major contributor to the fact that very few spreadsheets (< 10%) are correct.
I would recommend using a table to hold the source data and then use structured references to address the content.
= (1+10%)*Table1[@amount]would give a reference that may be copied down but my preference would be to use an array formula whenever possible
= (1+10%)*Table1[amount]I haven't used direct cell references or non-array formulas since 2015, so it is possible. That said, CSE array formulas were pretty nasty to work with!