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

[ericdarst]

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, the references in the other sheets also shift down.  I'm attempting to insert additional data in the master sheet without changing the other sheet references.  I've tried both absolute and relative cell references in the other sheets with no luck.  Also, copying values only doesn't work because I need to shift content down on the master sheet.

[#2 · Accepted solution]

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

[#3]

@OliverScheurich 

I've thought about using INDIRECT.  Will give it a try.  Thanks.

[#4]

@ericdarst 

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!

[#5]

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.