Excel automatically changing my formula

[Beal]

Hello,
This is a very annoying issue I have run in to and I don't know how to
work around it.

I have designed a sheet with 5 rows of blank cells followed by 2 rows
which sum up the previous 5. So the formulas work like this:

Cell | Formula
A1 | (empty)
A2 | (empty)
A3 | (empty)
A4 | (empty)
A5 | (empty)
A6 | "=sum(A1:A4)"
A7 | "=sum(A1:A5)"

The idea is that the 6th row sums the first 4 and the 7th row sums the
first 5 rows. Users enter data in the blank cells and the last two
rows give them two different summaries. The problem is that Excel
wants to "fix" my formula in cell A6. If a user enters a number in
cell A1, then A2, etc.. through A5, then when they enter that last
data point, Excel changes my formula in A6 so that it sums the first 5
rows instead of the first 4.

Note that if they enter data in cell A5 first, this problem does not
occur.

This acts like it is some kind of annoying (dare I say, dangerous?)
error checking feature and I desperately want it to stop. I am using
Office Professionsal '03 with SP3 installed. However the person who
brought this to me was using the sheet I created (on my computer) on
his computer, which has a newer version (Vista?) of Office installed
and this problem manifests when you enter data on either computer.

Anyone have any ideas?

 

[Gord Dibben]

=SUM($A$1:$A$4)
=SUM($A$1:$A$5)

or

=SUM(A$1:A$4)
=SUM(A$1:A$5)

As long as the row remains absolute.


Gord Dibben MS Excel MVP

 

[Prixt0n]

Hello,
This is a very annoying issue I have run in to and I don't know how to
work around it.

I have designed a sheet with 5 rows of blank cells followed by 2 rows
which sum up the previous 5.  So the formulas work like this:

Cell | Formula
A1 | (empty)
A2 | (empty)
A3 | (empty)
A4 | (empty)
A5 | (empty)
A6 | "=sum(A1:A4)"
A7 | "=sum(A1:A5)"

The idea is that the 6th row sums the first 4 and the 7th row sums the
first 5 rows.  Users enter data in the blank cells and the last two
rows give them two different summaries.  The problem is that Excel
wants to "fix" my formula in cell A6.  If a user enters a number in
cell A1, then A2, etc.. through A5, then when they enter that last















data point, Excel changes my formula in A6 so that it sums the first 5
rows instead of the first 4.

Note that if they enter data in cell A5 first, this problem does not
occur.

This acts like it is some kind of annoying (dare I say, dangerous?)
error checking feature and I desperately want it to stop.  I am using
Office Professionsal '03 with SP3 installed.  However the person who
brought this to me was using the sheet I created (on my computer) on
his computer, which has a newer version (Vista?) of Office installed
and this problem manifests when you enter data on either computer.

Anyone have any ideas?

In A6 use =sum($A$1:$A$4)
In A7 use =sum($A$1:$A$4)

 

[Prixt0n]

Sorry, wrote it wrong

In A6 use =sum($A$1:$A$4)
In A7 use =sum($A$1:$A$5)- Dölj citerad text -

- Visa citerad text -

 

[Ken Johnson]

Hello,
This is a very annoying issue I have run in to and I don't know how to
work around it.

I have designed a sheet with 5 rows of blank cells followed by 2 rows
which sum up the previous 5.  So the formulas work like this:

Cell | Formula
A1 | (empty)
A2 | (empty)
A3 | (empty)
A4 | (empty)
A5 | (empty)
A6 | "=sum(A1:A4)"
A7 | "=sum(A1:A5)"

The idea is that the 6th row sums the first 4 and the 7th row sums the
first 5 rows.  Users enter data in the blank cells and the last two
rows give them two different summaries.  The problem is that Excel
wants to "fix" my formula in cell A6.  If a user enters a number in
cell A1, then A2, etc.. through A5, then when they enter that last
data point, Excel changes my formula in A6 so that it sums the first 5
rows instead of the first 4.

Note that if they enter data in cell A5 first, this problem does not
occur.

This acts like it is some kind of annoying (dare I say, dangerous?)
error checking feature and I desperately want it to stop.  I am using
Office Professionsal '03 with SP3 installed.  However the person who
brought this to me was using the sheet I created (on my computer) on
his computer, which has a newer version (Vista?) of Office installed
and this problem manifests when you enter data on either computer.

Anyone have any ideas?

It's caused by the "Extend data range formats and formulas" option on
the "Edit" tab sheet of the "Options" dialog. So you could either use
absolute reference or deselect that option.

Ken Johnson

 

[Gord Dibben]

Never thought about that Ken

I leave mine disabled. I hate it.


Gord

 

[Beal]

Hello,
This is a very annoying issue I have run in to and I don't know how to
work around it.

I have designed a sheet with 5 rows of blank cells followed by 2 rows
which sum up the previous 5.  So the formulas work like this:

Cell | Formula
A1 | (empty)
A2 | (empty)
A3 | (empty)
A4 | (empty)
A5 | (empty)
A6 | "=sum(A1:A4)"
A7 | "=sum(A1:A5)"

The idea is that the 6th row sums the first 4 and the 7th row sums the
first 5 rows.  Users enter data in the blank cells and the last two
rows give them two different summaries.  The problem is that Excel
wants to "fix" my formula in cell A6.  If a user enters a number in
cell A1, then A2, etc.. through A5, then when they enter that last
data point, Excel changes my formula in A6 so that it sums the first 5
rows instead of the first 4.

Note that if they enter data in cell A5 first, this problem does not
occur.

This acts like it is some kind of annoying (dare I say, dangerous?)
error checking feature and I desperately want it to stop.  I am using
Office Professionsal '03 with SP3 installed.  However the person who
brought this to me was using the sheet I created (on my computer) on
his computer, which has a newer version (Vista?) of Office installed
and this problem manifests when you enter data on either computer.

Anyone have any ideas?

Thanks to everyone for the input.

 

[Ken Johnson]

Thanks to everyone for the input.

You're welcome.
Thanks for for raising the issue, it was quite an eye-opener.
Like Gord, I will now keep the "Extend data range formats and
formulas" turned off.

Ken Johnson