referencing the cell above

S

stuhag

Hi
I have a kin a formula wich gets the value from the cell above and then add
some.
What I get is an list of values incrementing.

When I delete one row in this list, all cells below 'crashes' with #REF!.
What I need is that for the formulas to always look one cell up, even if I
delete a row. Now it seems that if I delete Row8, then when Row9 become Row8
it reference to it self and then ov course goes bananas...

Hope I made myself understandable ;-)

Any ideas folks?
 
B

Bob Phillips

Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and
as it doesn't explicitly refer to A19, it doesn't fail when row 19 is
deleted.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

Stephen

stuhag said:
Hi
I have a kin a formula wich gets the value from the cell above and then
add
some.
What I get is an list of values incrementing.

When I delete one row in this list, all cells below 'crashes' with #REF!.
What I need is that for the formulas to always look one cell up, even if I
delete a row. Now it seems that if I delete Row8, then when Row9 become
Row8
it reference to it self and then ov course goes bananas...

Hope I made myself understandable ;-)

Any ideas folks?
Click to expand...

As an example, from cell A10, OFFSET(A10,-1,0) will reference the cell above
it. So the formula
=SUM(A1:OFFSET(A10,-1,0))
put in cell A10 will add up the column of cells above this cell. If cells
are added or deleted above it, the formula adjusts automatically.
Is this what you need?
 
S

stuhag

Well.. none of you exactly got my problem, but both of you gave med the
OFFSET function, and that seems to have solved what I wanted.

Thanks a lot for helping and the quick replies !
 
Top