cell reference change with insert row

[jcl9684]

Hey all --

I have a spreadsheet where I frequently insert a row near the top o
the page to make room for new updated data.

I have simple calculations on another worksheet that involve adding an
subtracting certain values below the area where i insert rows. When
insert a row, I want all of these formulas/calculations to use the sam
cell references instead of using the same piece of data. In othe
words, when all of the data below the inserted row basically "move
down" one row, I want all of the formulas to recalculate using the ne
data in the same numbered cell that it used to reference.

Even when I use the $ to "lock" the cell reference in the formula (i.e
$J$13), excel still changes the value to $J$14 when I insert a row.
want the value to remain $J$13. Any suggestions

 

[jcl9684]

One more note about the problem --

This process (inserting a row for new data, and wanting the cel
references in equations to NOT CHANGE due to the inserted row) needs t
be repeated daily. This, and a large volume of updates that would nee
to be made, is why I cannot manually change all of the equations bac
to their original form.

I would greatly appreciate any suggestions or help. Thanks for you
time.

Jak

 

[Niek Otten]

Store the address in a cell and use the INDIRECT() function. Like in
=INDIRECT(A1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Max]

One way is via using INDIRECT()

For example:

Instead of : =SUM(Sheet1!A1:C1)

use the functionally equivalent:
=SUM(INDIRECT("Sheet1!$A$1:$C$1"))

If you insert a new row at the top in Sheet1
the 1st formula will auto-change to: =SUM(Sheet1!A2:C2)
but the one using INDIRECT will always reference the top row of Sheet1

If you need to copy down the formula, use ROW() as the auto-incrementer
For copying across, use COLUMN() as the auto-incrementer

For example, in Sheet2, instead of using the "static"
: =SUM(INDIRECT("Sheet1!$A$1:$C$1"))

Put in a cell in row1, say, in A1, the equivalent:
=SUM(INDIRECT("Sheet1!$A$"&ROW()&":$C$"&ROW()))

A1 can then be copied down to sum the corresponding ranges in row2, row3,
etc of Sheet1