Excel 2007 Automatically changing formulas?! 10

[ColourfulFigsnDiags]

Ok so I have a bunch of numbers in cells A1, B2 C1 and D1. In cell E1 I have a formula which sums cells A1, B1 and C1.

I fill that formula down a pile of rows, and look at cell (for example) E7 and sure enough the formula still says =sum(A7:C7)

But, for some odd reason, when I enter the numbers in cells A7, B7, C7, and D7, the formula changes, automatically, to =Sum(A77) !!!! I do NOT want cell D7 in the sum.

Is this a stupid autocorrect thing? If so it is bloody dangerous and I want to turn it off.

Any thoughts?

Read the Eng-Tips Site Policies at FAQ731-376

 

[CodeRef]

A colorful star for ColourfulFigsnDiags!

<a href="

http://www.authorizedinspector.com"><img

src="

http://www.authorizedinspector.com/Images/AILogo.gif"

</a>

 

[IFRs]

My excel only does this for horizontal sums - not vertical ones!

 

[jhardy1]

"My excel only does this for horizontal sums - not vertical ones!"

Interesting .... my copy of Excel 2003 (SP3) changes the formula on both horizontal sums and vertical sums if I enter a number into the blank row or column.

 

[IFRs]

For my vertical sums, Excel puts one of those little triangles in the cell corner and a pulldown menu next to the cell that ASKS if I want the formula extended. Both for 2003 and 2007 versions.

This is all academic for me because I use OpenOffice 99% of the time anyway. But, interesting!!

 

[chichuck]

I think this is dangerous as well. I've seen this before and couldn't figure out what was happening. I think the default ought to be unchecked, and that checking that box should produce a big red letter warning.

The paragraph from the help does not explain how this feature behaves.

Last, I went one step further with my version of Excel 2003 SP3. When I went and deleted the number in cell D1, the formula did not change back.

IRstuff, can you please inform Bill about this?


Regards,

chichuck

 

[IRstuff]

I'll get right on that...

TTFN

FAQ731-376

 

[IDS]

IFRS - In 2007 I get the behaviour you describe if the "extend formulas and formats" option is turned off, or if it is turned on but the formula only covers two rows.

If the option is turned on, and the formula covers three or more rows it automatically etends the formula without asking and without warning (unless you call briefly highlighting the cell with the formula a warning).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LobstaEata]

Funny...I tried it and the auto extend didn't activate, even when the box in the edit tab was still checked. Could it be that Bill has been listening all this time?