Formulas do not update

Excel 2003 - Formulas on one worksheet (pMon) referring to another worksheet
(pCSRs) all-of-a-sudden stopped updating when the information on the second
worksheet changed. These are simple formulas without calculation, such as
=pCSRs!F92. Calculation is set to automatic, and even when I press F9 they
don't update.

This has worked for a long time, but I have recently been making changes to
other worksheets by importing data from another similar workbook. However
none of the problem formulas involved have a reference to the other workbook
(nothing like ='[Workbook2]pCSRs'!F92). Any ideas?

I've never seen this in real life, but there have been plenty of posts that
describe what you see--but it's not usual. It's usually one file that's giving
the user problems.

One solution that seems to work is to group all the worksheets
rightclick on any worksheet and choose "Select all sheets"
Then select all the cells on that activesheet (and all the cells on the other
sheets in the group)
(ctrl-a (maybe a few times, depending on the version of excel you're using)

Then Edit|Replace
what: = (equal sign)
with: = (equal sign)
replace all

Then ungroup the sheets
rightclick on any tab of the grouped sheets. Select Ungroup Sheets.

This last step is very important. Almost any change you make to a member of the
grouped sheets is made to the others in that group.

By changing the = to =, you'll be forcing excel to recalculate every formula.
(You could do this worksheet by worksheet, too.)

And it usually wakes up excel's calculation engine.

AZSteve wrote:

Excel 2003 - Formulas on one worksheet (pMon) referring to another worksheet
(pCSRs) all-of-a-sudden stopped updating when the information on the second
worksheet changed. These are simple formulas without calculation, such as
=pCSRs!F92. Calculation is set to automatic, and even when I press F9 they
don't update.

This has worked for a long time, but I have recently been making changes to
other worksheets by importing data from another similar workbook. However
none of the problem formulas involved have a reference to the other workbook
(nothing like ='[Workbook2]pCSRs'!F92). Any ideas?

--

Dave Peterson

Sharpshooter, you can add another notch to your "it works" gun. Thanks.

"Dave Peterson" wrote:

I've never seen this in real life, but there have been plenty of posts that
describe what you see--but it's not usual. It's usually one file that's giving
the user problems.

One solution that seems to work is to group all the worksheets
rightclick on any worksheet and choose "Select all sheets"
Then select all the cells on that activesheet (and all the cells on the other
sheets in the group)
(ctrl-a (maybe a few times, depending on the version of excel you're using)

Then Edit|Replace
what: = (equal sign)
with: = (equal sign)
replace all

Then ungroup the sheets
rightclick on any tab of the grouped sheets. Select Ungroup Sheets.

This last step is very important. Almost any change you make to a member of the
grouped sheets is made to the others in that group.

By changing the = to =, you'll be forcing excel to recalculate every formula.
(You could do this worksheet by worksheet, too.)

And it usually wakes up excel's calculation engine.

AZSteve wrote:

Excel 2003 - Formulas on one worksheet (pMon) referring to another worksheet
(pCSRs) all-of-a-sudden stopped updating when the information on the second
worksheet changed. These are simple formulas without calculation, such as
=pCSRs!F92. Calculation is set to automatic, and even when I press F9 they
don't update.

This has worked for a long time, but I have recently been making changes to
other worksheets by importing data from another similar workbook. However
none of the problem formulas involved have a reference to the other workbook
(nothing like ='[Workbook2]pCSRs'!F92). Any ideas?

--

Dave Peterson
.