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...

excel

Formulas and Functions

OliverScheurich
Apr 26, 2024
ericdarst
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.

OliverScheurich

Gold Contributor

Apr 26, 2024

ericdarst

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.

ericdarst

Copper Contributor

Apr 27, 2024

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.

Forum Discussion

Inserting copied cells in one sheet without shifting cell references in another sheet

Resources