Insert New Row without shifting formula

[DJ]

e.g. I have a formula in cell B4 that calculates data in column cells
C4 to, say, M4. (e.g. "=Sum($C4.$M4)") I then wish to insert a new C4
row, copy a new set of data into the new C4, but use the same formula
in B4 to calculate the new totals ("=Sum($C4.$M4)"). However, when I
insert the new row for C4, the formula in B4 shifts to now say
"=Sum($D4.$N4)". Is there an easy way to make this work ?

 

[Guest]

Possibly:

=Sum(Indirect("$C4.$M4"))

The down side is you won't be able to copy this formula across/down and have
excel automatically change the row reference (if this formula needs to be
copied).

 

[Guest]

Sorry - I did not notice you're summing two cells, not a range (comma vs
colon).


SUM(INDIRECT("$C4"),INDIRECT("$M4"))