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.

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.

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.

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.

Great this helps!
Thanks a lot!!!

"Chip Pearson" wrote:

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.

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")) ???

"fruitchunk" wrote:

Great this helps!
Thanks a lot!!!

"Chip Pearson" wrote:

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)

Try this:

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

--
Biff
Microsoft Excel MVP


"fruitchunk" wrote in message
...
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")) ???

"fruitchunk" wrote:

Great this helps!
Thanks a lot!!!

"Chip Pearson" wrote:

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)

Thanks to all of you!