Forum Discussion
Solved
Inserting copied cells in one sheet without shifting cell references in another sheet
I have a master sheet with multiple entry rows and other sheets that reference cells in the master sheet. When I insert copied cells from another source into the master sheet and shift cells down, t...
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.
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.
Because the copied cell blocks are consistent in size the ROW function makes it work. Had to make sure the other sheets accommodated the same cell block size by adding some zero height rows but that worked nicely. Kept me from having to calculate the row.