How do I fix cell refs when adding rows

D

Dave L

Is there any way for me to insert a row without changing a cell ref in a
formula eg =sum(a1:a5) instead of changing to A2:A6 I want A1:A6 ie the range
increases in line with the added row.

TIA
Dave
 
S

Stefi

Sorry, I didn't test my answer thoroughly! I made additional test and got the
following: if you insert new row before row 1 then formula changes to
=SUM(A2:A6).
if you insert new row before row 6 (row of SUM), then formula remains
=SUM(A1:A5) with an empty row 6.
But if you insert new row before any other row (2-5) then formula changes to
=SUM(A1:A6).

Regards,
Stefi

„Dave L†ezt írta:
 
R

Ron Coderre

I think this does what you want:
=SUM(INDIRECT("A1:A"&ROW(A5)))

Does that help?

***********
Regards,
Ron
 
Top