breaking the laws of excel

  • Thread starter Thread starter dale5535
  • Start date Start date
D

dale5535

As many of you know when you have a formula that pulls from various
cells and you insert additional cells or rows the formula moves with
the inserted cells. Is there a way to avoid this? Can you put in a
prefix in a formula so that even if you add additional lines it always
pulls from the orginal cell(s).

For example you a have a formula in cell A1 that adds B1 and C1
together. If you insert cells above B1 and C1 the data moves to B2 and
C2 and formula in A1 moves with it and now has the result of B2 + C2
instead of B1 and C1. How can I keep the formula so that it still adds
B1 and C1 instead of moving.

I hope I have made myself clear. Thanks.
 
Hi dale,
If I understand you correctly, absolute reference the cells in your formulae, (using $)
ie.
A1 = B$1 + C$1 (if only rows are to be added)...
A1 = $B1 + $C1 (if only columns are to be added)...
A1 = $B$1 + $C$1 (if both columns & rows are to be added).

Yours,
Mathew


As many of you know when you have a formula that pulls from various
cells and you insert additional cells or rows the formula moves with
the inserted cells. Is there a way to avoid this? Can you put in a
prefix in a formula so that even if you add additional lines it always
pulls from the orginal cell(s).

For example you a have a formula in cell A1 that adds B1 and C1
together. If you insert cells above B1 and C1 the data moves to B2 and
C2 and formula in A1 moves with it and now has the result of B2 + C2
instead of B1 and C1. How can I keep the formula so that it still adds
B1 and C1 instead of moving.

I hope I have made myself clear. Thanks.



------------------------------------------------
 
Thanks Matthew,

I did try to use absolute values, however the formula changes as the
cells are inserted even when the absolute sign is present. Anyone
else want to take a shot?
 
Absolutes will not do it. You need to use the INDIRECT function:-

=SUM(INDIRECT("B1:C1"))
 
One way:

A1: =INDIRECT("B1")+INDIRECT("C1")

Another:

A1: =SUM(OFFSET(A1,-ROW()+1,1,1,2))
 
Back
Top