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

Attached Files

Test Formula Change.xlsx (10.7 KB, 8 views)

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.

__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules

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!

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

__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules

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

__________________

Office 2007
Any Feedback Welcomed

If you receive a solution....Please let us know:

1) Click the EDIT button on your first post
2) Click the GO ADVANCED button
3) Select SOLVED from the Title dropdown (under the Reason for Editing box)
4) Click the SAVE CHANGES button

Note: If it's more than 2 days after your first post, please ask a moderator to mark the thread SOLVED for you.

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