Excel automatically changing my formula

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
Wed Oct 08 16:57:42 CDT 2008

=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

On Wed, 8 Oct 2008 14:42:11 -0700 (PDT), Beal <bealrabbitslayer@hotmail.com>
wrote:

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

Prixt0n
Wed Oct 08 17:02:04 CDT 2008

On 8 Okt, 23:42, Beal <bealrabbitsla...@hotmail.com> wrote:
> 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. =A0So the formulas work like this:
>
> Cell | Formula
> A1 | (empty)
> A2 | (empty)
> A3 | (empty)
> A4 | (empty)
> A5 | (empty)
> A6 | "=3Dsum(A1:A4)"
> A7 | "=3Dsum(A1:A5)"
>
> The idea is that the 6th row sums the first 4 and the 7th row sums the
> first 5 rows. =A0Users enter data in the blank cells and the last two
> rows give them two different summaries. =A0The problem is that Excel
> wants to "fix" my formula in cell A6. =A0If 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. =A0I am using
> Office Professionsal '03 with SP3 installed. =A0However 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 =3Dsum($A$1:$A$4)
In A7 use =3Dsum($A$1:$A$4)

Prixt0n
Wed Oct 08 17:03:51 CDT 2008

On 9 Okt, 00:02, Prixt0n <lei...@gmail.com> wrote:
> On 8 Okt, 23:42, Beal <bealrabbitsla...@hotmail.com> wrote:
>
>
>
>
>
> > 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. =A0So the formulas work like this:
>
> > Cell | Formula
> > A1 | (empty)
> > A2 | (empty)
> > A3 | (empty)
> > A4 | (empty)
> > A5 | (empty)
> > A6 | "=3Dsum(A1:A4)"
> > A7 | "=3Dsum(A1:A5)"
>
> > The idea is that the 6th row sums the first 4 and the 7th row sums the
> > first 5 rows. =A0Users enter data in the blank cells and the last two
> > rows give them two different summaries. =A0The problem is that Excel
> > wants to "fix" my formula in cell A6. =A0If 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. =A0I am using
> > Office Professionsal '03 with SP3 installed. =A0However 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?
Sorry, wrote it wrong
>
> In A6 use =3Dsum($A$1:$A$4)
> In A7 use =3Dsum($A$1:$A$5)- D=F6lj citerad text -
>
> - Visa citerad text -

Ken
Wed Oct 08 18:06:52 CDT 2008

On Oct 9, 8:42=A0am, Beal <bealrabbitsla...@hotmail.com> wrote:
> 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. =A0So the formulas work like this:
>
> Cell | Formula
> A1 | (empty)
> A2 | (empty)
> A3 | (empty)
> A4 | (empty)
> A5 | (empty)
> A6 | "=3Dsum(A1:A4)"
> A7 | "=3Dsum(A1:A5)"
>
> The idea is that the 6th row sums the first 4 and the 7th row sums the
> first 5 rows. =A0Users enter data in the blank cells and the last two
> rows give them two different summaries. =A0The problem is that Excel
> wants to "fix" my formula in cell A6. =A0If 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. =A0I am using
> Office Professionsal '03 with SP3 installed. =A0However 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
Wed Oct 08 18:45:04 CDT 2008

Never thought about that Ken

I leave mine disabled. I hate it.


Gord

On Wed, 8 Oct 2008 16:06:52 -0700 (PDT), Ken Johnson <KenCJohnson@gmail.com>
wrote:

>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.

Beal
Thu Oct 09 10:11:46 CDT 2008

On Oct 8, 3:42=A0pm, Beal <bealrabbitsla...@hotmail.com> wrote:
> 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. =A0So the formulas work like this:
>
> Cell | Formula
> A1 | (empty)
> A2 | (empty)
> A3 | (empty)
> A4 | (empty)
> A5 | (empty)
> A6 | "=3Dsum(A1:A4)"
> A7 | "=3Dsum(A1:A5)"
>
> The idea is that the 6th row sums the first 4 and the 7th row sums the
> first 5 rows. =A0Users enter data in the blank cells and the last two
> rows give them two different summaries. =A0The problem is that Excel
> wants to "fix" my formula in cell A6. =A0If 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. =A0I am using
> Office Professionsal '03 with SP3 installed. =A0However 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
Thu Oct 09 16:46:37 CDT 2008

On Oct 10, 2:11=A0am, Beal <bealrabbitsla...@hotmail.com> wrote:
> On Oct 8, 3:42=A0pm, Beal <bealrabbitsla...@hotmail.com> wrote:
>
>
>
> > 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. =A0So the formulas work like this:
>
> > Cell | Formula
> > A1 | (empty)
> > A2 | (empty)
> > A3 | (empty)
> > A4 | (empty)
> > A5 | (empty)
> > A6 | "=3Dsum(A1:A4)"
> > A7 | "=3Dsum(A1:A5)"
>
> > The idea is that the 6th row sums the first 4 and the 7th row sums the
> > first 5 rows. =A0Users enter data in the blank cells and the last two
> > rows give them two different summaries. =A0The problem is that Excel
> > wants to "fix" my formula in cell A6. =A0If 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. =A0I am using
> > Office Professionsal '03 with SP3 installed. =A0However 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.

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