breaking the laws of excel

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

Mathew P Bennett

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.



------------------------------------------------
 
D

dale5535

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?
 
K

Ken Wright

Absolutes will not do it. You need to use the INDIRECT function:-

=SUM(INDIRECT("B1:C1"))
 
J

J.E. McGimpsey

One way:

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

Another:

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