How do I fix cell refs when adding rows

[Guest]

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

 

[Guest]

=sum(a$1:a5)

Regards,
Stefi

„Dave L†ezt írta:

 

[Guest]

$ doesn't work when you insert extra rows

=sum(a$1:a5)

Regards,
Stefi

„Dave L†ezt írta:

 

[Guest]

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:

 

[Guest]

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

Does that help?

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

 

[Guest]

Thanks Ron and Steffi

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

Does that help?

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