Excel formula will not recalculate when cell value changes

[berghell]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

My Excel problem in that a correct formula to multiply two cells will not recalculate at all when a quantity value changes. If I erase the formula and put the same formula back into the cell it will recalculate properly. I have to do this to every cell with a formula. Somehow the spreadsheet is corrupted. Any ideas on a solution.

 

[berghell]

I figured it out. The default setting for formula recalculation was set to "manual" rather than "automatic". I never set it to manual so I don't know how it got that way.

 

[frobozz]

OK, I have this problem for real (my setting for recalc is "Automatic" and I still have issues.)

On Sheet1 I have a cell that is set to

='Sheet2'!D327

If I type something into D327 on Sheet2, it shows up in the cell on Sheet1, as expected.

If I paste in a huge tab-delimited set of text data onto Sheet2 (which is its purpose), including a change in the value at D327, it does not show up as changed on Sheet1! Lots of other things on Sheet1, which are actual formulas and calculations based on Sheet2 data, do show up with the right values as near as I can tell. It's only this set of ='Sheet2'!xxxx values that don't change.

If I save the workbook and close it and open it again, the cell still hasn't changed.

If I select the cell and hit Tab or Return it doesn't change. If I select the cell and put my cursor in the Formula bar and hit return (i.e. without editing the formula at all) it *does* change! If I edit the formula on that cell it obviously changes the value, but if I then "Undo" it does not revert to the original improper value, rather it now correctly reflects the value of D327 on Sheet2! If, while it's in this mode of refusing to change, I go manually type a new value into D327 on Sheet2, then the value is immediately reflected on Sheet1. It's only when I paste in my 5 columnsx350 rows of data that I get into this trap.

Doing a manual recalculation with the keyboard commands or from the buttons on the preferences screen does not update the cell on Sheet1.

So it's almost as if because it's not an actual formula, but rather just a copy of a cell value, it's not getting recalculated in most of the normal circumstances.

Let me repeat that I have auto recalc turned on, and the vast majority of the cells in this spreadsheet do in fact automatically recalculate... including this one, under some circumstances. But when it doesn't recalculate during the massive data paste, it's somehow getting flagged as not needing recalculation even under many of the circumstances where I am supposedly forcing it.

This, and a few other errors, are making it pretty much impossible to use Excel 2008 as a production tool. We're all using 2004 while waiting for the bug fixes... which isn't easy because of the bug preventing anything but 2008 being set as the default app for Excel files!

Duncan
Duncan

 

[Bob Greenblatt]

OK, I have this problem for real (my setting for recalc is "Automatic" and I
still have issues.)

On Sheet1 I have a cell that is set to

='Sheet2'!D327

If I type something into D327 on Sheet2, it shows up in the cell on Sheet1, as
expected.

If I paste in a huge tab-delimited set of text data onto Sheet2 (which is its
purpose), including a change in the value at D327, it does not show up as
changed on Sheet1! Lots of other things on Sheet1, which are actual formulas
and calculations based on Sheet2 data, do show up with the right values as
near as I can tell. It's only this set of ='Sheet2'!xxxx values that don't
change.

If I save the workbook and close it and open it again, the cell still hasn't
changed.

If I select the cell and hit Tab or Return it doesn't change. If I select the
cell and put my cursor in the Formula bar and hit return (i.e. without editing
the formula at all) it *does* change! If I edit the formula on that cell it
obviously changes the value, but if I then "Undo" it does not revert to the
original improper value, rather it now correctly reflects the value of D327 on
Sheet2! If, while it's in this mode of refusing to change, I go manually type
a new value into D327 on Sheet2, then the value is immediately reflected on
Sheet1. It's only when I paste in my 5 columnsx350 rows of data that I get
into this trap.

Doing a manual recalculation with the keyboard commands or from the buttons on
the preferences screen does not update the cell on Sheet1.

So it's almost as if because it's not an actual formula, but rather just a
copy of a cell value, it's not getting recalculated in most of the normal
circumstances.

Let me repeat that I have auto recalc turned on, and the vast majority of the
cells in this spreadsheet do in fact automatically recalculate... including
this one, under some circumstances. But when it doesn't recalculate during the
massive data paste, it's somehow getting flagged as not needing recalculation
even under many of the circumstances where I am supposedly forcing it.

This, and a few other errors, are making it pretty much impossible to use
Excel 2008 as a production tool. We're all using 2004 while waiting for the
bug fixes... which isn't easy because of the bug preventing anything but 2008
being set as the default app for Excel files!

Duncan
Duncan

Is the formula within a data table? Is this workbook newly created, or has
it been around for a while. By any chance did it originate on a windows
machine?

Try copying the contents of each worksheet into a new sheet in a new
workbook, by highlighting the diamond in the upper left corner at the
intersection of the row and column headers, copying and pasting into a new
sheet. After you have duplicated the workbook this way close the original,
and save the new one as a new name. Quit and restart Excel, open the new
workbook and paste in the values. Does it calculate properly now?

 

[frobozz]

The formula (the "='sheet'!cell" value) is not in a data table. The workbook has been around a while and was created on Windows. We only migrated to Macs about a year ago.

Your suggestion of re-creating the workbook in a newly created one is intriguing, but is going to be a bit of work, as it is 14 sheets in the book, using lots of 'sheet_name'! references so I have to go rename all the tabs properly and everything.

BUT, I see a killer problem already: simply doing a "paste" into the new sheet, (properly) makes all the cells refer to the old workbook! So they are now '[filename]sheet_name'!cell references! That's no good, obviously. And none of the Paste Special choices seem to do what I need. I need formulas localized, plus formatting. Is there a way to do that?

Interestingly, after doing the paste, my original sheet is still showing the wrong non-recalc'ed number, while the new sheet is showing the correct number!

And just for completeness' sake I'll point out that this sheet works just dandy in Excel 2004, and the ability to reuse sheets from years ago is one of the main reasons we stay with Excel. If we have to recreate all our sheets to use Excel 2008 then we have more products we can be looking at as alternatives.

Duncan

 

[Bob Greenblatt]

The formula (the "='sheet'!cell" value) is not in a data table. The workbook
has been around a while and was created on Windows. We only migrated to Macs
about a year ago.

Your suggestion of re-creating the workbook in a newly created one is
intriguing, but is going to be a bit of work, as it is 14 sheets in the book,
using lots of 'sheet_name'! references so I have to go rename all the tabs
properly and everything.

BUT, I see a killer problem already: simply doing a "paste" into the new
sheet, (properly) makes all the cells refer to the old workbook! So they are
now '[filename]sheet_name'!cell references! That's no good, obviously. And
none of the Paste Special choices seem to do what I need. I need formulas
localized, plus formatting. Is there a way to do that?

Interestingly, after doing the paste, my original sheet is still showing the
wrong non-recalc'ed number, while the new sheet is showing the correct number!

And just for completeness' sake I'll point out that this sheet works just
dandy in Excel 2004, and the ability to reuse sheets from years ago is one of
the main reasons we stay with Excel. If we have to recreate all our sheets to
use Excel 2008 then we have more products we can be looking at as
alternatives.

Duncan

Interesting that the pasted sheet has the correct values. Yes, the paste
will generate the links. You could then break the links. However, there is
an update "in the wind" which might fix the problem. You could choose to
keep watching Mactopia for an announcement and wait to see if the update
fixes it. Since it is OK in 2004, the update may correct this.

 

[frobozz]

Interesting that the pasted sheet has the correct values. Yes, the paste

will generate the links. You could then break the links. However, there is
an update "in the wind" which might fix the problem. You could choose to
keep watching Mactopia for an announcement and wait to see if the update
fixes it. Since it is OK in 2004, the update may correct this.

Ooooo, I eagerly await an update!!

I did one more test, just to try to figure out what's going on...

If I take the sheet in its broken state (mass data pasted in, but mirror cell not showing updated value) and open it in Excel 2004... it stays with the wrong old value. Even if I do the manual calculation trick (using the buttons in Preferences, or the keyboard shortcuts). So it looks like mirrored values not getting updated with a forced recalc is a longstanding problem. The main problem is the cell not updating when the data is first pasted.

So I opened the previous version of the sheet in Excel 2004, and pasted in the mass data. As expected, the mirrored cell updated fine.

So that's what broke with Excel 2008: It somehow misses updating that mirrored cell when a bunch of text data is pasted in at once. All the other stuff about forced-recalc not then updating it, that seems to be an old issue. But a non-issue if the first problem is fixed!

Duncan

 

[frobozz]

OK, because this is a similar issue, I'm going to post it slightly off-topic here. I've tried twice to post it as a new thread in the Excel Forum, but it keeps showing up as if it were in the generic "Mactopia forum" whatever that is. Nobody looking at the Excel forum would ever see those posts.

* *****************************************

We're still trying to track down the bogus "circular reference" errors that sometimes crop up under Excel 2008 (but not in 2004)...and as one path of investigation I was using the "Compatibility check" toolbox tool. This seems like a bug, and it doesn't occur in Excel 2004, and here is how to trivially recreate it:

Open up a new spreadsheet.

Add a second sheet.

Type any value into a cell on the first sheet.

On the second sheet, pick a cell and type = in it then navigate to the first sheet and click on that cell with the value, then return.

As expected, the value in the cell on the first sheet is now reflected on the second sheet. But if you test compatibility, it will flag the second sheet cell as "Formula contains a link to a closed workbook."

Yes this error is bogus and easily enough ignored, but it gives it for every group of them on every sheet in some of my big workbooks, and it's annoying to have to figure out which errors are real and which are this error.

Unlike the false circular-reference problem, this one is 100% repeatable, with just this simple an example.

Duncan

 

[frobozz]

Downloaded SP1 and here's the score:

-- If I take that sheet with the mirrored cell that didn't update (it is saved in that state) and open it in Excel 08... it updates the cell as it opens! Yay!

-- If I take the previous sheet and do the mass paste... it does not update the cell. Well, drat! Manual recalc still doesn't get it either. Interestingly, doing a *save-as* on the file does update it! What is that doing that a manual recalc is not? (But if I just do a normal *save* it does not update it. Argh!)

-- Bogus compatibility errors: not fixed

-- Bogus circular reference problems: not fixed.

Duncan