FORMULA CHANGES

[CLEM]

First the spreadsheet we are talking about here was created in Excel '97 and
I am now using Excel 2003.
In each row I have a formula in column P that sums cells in the I & J
columns.
Ex.(=SUM(I1:J1)

I am having a problem when I am filling out a row and enter something into
the cell in the I column then enter a figure into the K COLUMN it is changing
the fomula in column P to =SUM(I1:K1). IN OTHERWORDS, IT IS NOW INCLUDING
THE CELL IN COLUMN K.
Now, what is even more interesting is when I don't enter anything in to I
column but enter something into J column it doesn't change the formula. It
only changes the formula when I make an entry into the I column cells. And
it only changes when I fill the row from left to right (A1,B1,C1,ETC) AND
WHEN I ENTER THE FIGURE INTO THE K COLUMN CELL. THAT IS WHEN THE FORMULA
CHANGES IN THE P COLUMN TO INCLUDE THE K COLUMN CELL.
HELP!!! What is going on here?

 

[Gary''s Student]

Tools > Options... > Edit > clear the extend list formats and formulas
checkbox.

 

[Ken Johnson]

First the spreadsheet we are talking about here was created in Excel '97 and
I am now using Excel 2003.
In each row I have a formula in column P that sums cells in the I & J
columns.
Ex.(=SUM(I1:J1)

I am having a problem when I am filling out a row and enter something into
the cell in the I column then enter a figure into the K COLUMN it is changing
the fomula in column P to =SUM(I1:K1). IN OTHERWORDS, IT IS NOW INCLUDING
THE CELL IN COLUMN K.
Now, what is even more interesting is when I don't enter anything in to I
column but enter something into J column it doesn't change the formula. It
only changes the formula when I make an entry into the I column cells. And
it only changes when I fill the row from left to right (A1,B1,C1,ETC) AND
WHEN I ENTER THE FIGURE INTO THE K COLUMN CELL. THAT IS WHEN THE FORMULA
CHANGES IN THE P COLUMN TO INCLUDE THE K COLUMN CELL.
HELP!!! What is going on here?

Hi CLEM,

The only way I can get my xl2003 to do that is by literally dragging a
value out of a J cell and dropping it into the K cell on the same
row.
The solution for preventing formula change when a user drags and drops
cell values is to use the INDIRECT function for referring to the range
to be summed...

=SUM(INDIRECT("I"&ROW()&":J"&ROW()))

It could be worth a try on your sheet.

Ken Johnson