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

=sum(a$1:a5)

Regards,
Stefi

€žDave L€� ezt Ã*rta:

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

$ doesn't work when you insert extra rows

"Stefi" wrote:

=sum(a$1:a5)

Regards,
Stefi

€žDave L€� ezt Ã*rta:

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

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:

$ doesn't work when you insert extra rows

"Stefi" wrote:

=sum(a$1:a5)

Regards,
Stefi

€žDave L€� ezt Ã*rta:

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

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

Does that help?

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


"Dave L" wrote:

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

Thanks Ron and Steffi

"Ron Coderre" wrote:

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

Does that help?

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


"Dave L" wrote:

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