cell reference change with insert row

  • Thread starter Thread starter jcl9684
  • Start date Start date
J

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
 
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
 
Store the address in a cell and use the INDIRECT() function. Like in
=INDIRECT(A1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
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
 
Back
Top