How to stop to adjust references automatically

[fruitchunk]

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.

 

[FSt1]

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]

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

 

[Chip Pearson]

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]

Great this helps!
Thanks a lot!!!

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]

oh I forgot to mention that if I want to reference it to cells on sheet 1
where will I put "Sheet1!" in this formula =SUM(INDIRECT("A4:A8")) ???

 

[T. Valko]

Try this:

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

 

[fruitchunk]

Thanks to all of you!