Excel 2007 : Formula changes on the fly...

[CWatsonJr]

Here is a quirk from our wonderful new version 2007.

I have a column of numbers that I am adding with SUM. There is one cell that I don't want included in the SUM and if I put a number in there, Excel thinks I made a mistake and changes the formula for me....

I have attached a small section of the spreadsheet showing the quirk. Go ahead, stick a number in cell E16 and watch Excel do its thing....

Friggin' frustrating!!!!!

[NBVC]

I don't have 2007, but I have a convertor... and in the 2003 converted version I get the same thing....

But I can fix it by making the Sum range absolute...

=SUM($E$9:$E$15)

Try that.

[CWatsonJr]

making the formulas absolute won't really work for this spreadsheet. This is only a very small snippet of a very large sheet. The formulas are copy and pasted from week to week, so if they were made absolute, each day would have to be changed by hand...

no... the solution would be for Excel to accept it doesn't think it knows what I want!

[NBVC]

Again, not sure about where to find it in 2007.... but in 2003, if you go to Tools|Options and Edit tab... deselect the Extend data range formats and formulas checkbox.

I guess in 2007, click the Microsoft Office button and then click Excel Options..

[ratcat]

G'day

What a pain !!!!

What you can do is click on your Office button

Excel Option > Advanced > Editing Options > 'Extend data range formats and formulas' untick the box and click ok.

Its the sixth box down from the top.

HTH

[CWatsonJr]

Thank you very much. I didn't even know that option existed. I don't even know why people would want Excel to do that!

Thanks again!!!!!!

Cliff Watson