Try

=SUM(INDIRECT("A4:A8"))

This will always point to A4:A8, regardless of any copy/paste and
insert/delete operations.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"fruitchunk" wrote in message
...
Thanks I tried it but it doesn't work

A1=SUM($A$4:$A$8)
I inserted a row on A4 and now it's A1=SUM($A$5:$A$9)
So I undo and inserted a row on A5 now it's A1=SUM($A$4:$A$9)

I want A1 should always be =SUM(A4:A8) is it possible?

Thank

"FSt1" wrote:

hi
use absolute references
=sum($A$4:$A$8)
the $ signs will force xl to keep the references.
relative references change as you move the formula around the sheet,
keeping
it's references relative to it's new positions.
read up on absolute references vs. relative references in help.

regards
FSt1

"fruitchunk" wrote:

I would like to know how to stop excel from adjusting references
automatically when inserting a new row.

Example:
A1=SUM(A4:A8)

If I insert a new row on A4 the formula on A1 will now be =SUM(A5:A9)
How can I stop it from adjusting automatically,
I want A1 should always be =SUM(A4:A8) no matter if I insert a new row.

Please help, Thanks.