Formula not recalculating

[Kevin Vaughn]

I am stumped. I have tried all of the usual fixes, including ones I found
here:
http://www.mvps.org/dmcritchie/excel/formula.htm#problems

I have formulas that, no matter what I try, will not update after I make a
change to data that should cause the formula to recalculate.
I have checked (and rechecked ad nauseum) that the cells are not formatted
as text, that calculation is set to automatic, that the spreadsheet is not
set to display formulas. I have used F9, ctrl-alt-f9. I have removed all
formatting from the cells (I was using Styles as recommended in Professional
Excel Development) I have changed the formulas from using dynamic named
ranges to a normal sumproduct formula:
=SUMPRODUCT(--(Status!$F$7:$F$1435 = $F8), --(Status!$M$7:$M$1435 =
"Applicants"), --(Status!$H$7:$H$1435 = N$6))
When I change = to = using cntrl-h, the formulas do update, but then I go
make a change to one of the values to test it and it does not update. But if
I hit F2 and then enter, the correct number shows up.
I don't know what else to try. I have a feeling that no one is going to be
able to help me as I really do believe I have tried everything. I checked
all of my options, nothing funny like transitions or anything like that. I
have quit excel and restarted, and logged all the way out and restarted my
computer. I have looked at a different workbook, changed data, looked at the
cell with the formula and the cell was updated.
I don't know if it has anything to do with it, but this is a workbook
created from a template that I saved to my startup folder as book (again,
after having made some changes based on things I got from PED.)

I hope someone can pull a rabbit out of the hat. At this point all I can
think to try is forego all of the changes that I have entered to this
spreadsheet (but not the spreadsheet that it is getting the information
from.) Probably get rid of the book.xlt file, open a new worksheet, copy it
to this workbook, recreate all of the formulas/headings etc. but this time
without using styles.

 

[Peo Sjoblom]

Kevin,

When that has happened to me it usually meant the workbook was getting
corrupt (I think I have seen it happens twice in all my Excel life), I would
copy over all data to another workbook ASAP
Both times it happened to me it was something someone else had done and I
believe originally created in Lotus, then carried over to Excel 95 and later
Excel 97.. All I could do to update was the ctrl +h replacing = with = until
I copied all the data to a new workbook. Maybe someone else can help you a
bit more

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Kevin Vaughn]

Thanks for the suggestion. I am definitely going to try this. I'm not sure
why it would be corrupt though as it is a "young" file only about a week old
so I haven't had time to do too much damage to it, I believe. But still,
that does sound like a good suggestion. Thanks!

 

[Kevin Vaughn]

It didn't seem to work. I copied the relevant sheets to a new workbook and
it is still exhibiting the same behavior. I copied the first worksheet by
right-clicking and doing a copy that way, but I was told that I had cells >
512 characters, so I ended up copying by cell and then renaming my first
attempt to bak and working off the sheet I had copied by cell. Tried the
formulas both ways, as they were and then (after changing all of the named
formulas to reflect my change) changing formula to use dynamic ranges and
they won't update at all. If I try copying a formula that has a 0, all of
the cells end up 0, no form of recalcing will change, but then I use cntl-h
to change = to = and the formulas update. I again checked format of cells
(this time some of them were number not general as that is what I was trying
before I copied over to the new workbook. I also checked calculation in the
new workbook, and it was automatic.

Thanks anyway.

 

[Charles Williams]

If you have Excel 2002 or later you could try Ctrl-Alt-Shift-F9 which
rebuilds the dependency trees.

How big is the workbook?

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Kevin Vaughn]

Nope, I'm using 2000. It is 738 KB. Thanks.

 

[Kevin Vaughn]

I seem to have found a workable solution (Phew!)

I still don't know why, but what I did was this. I created a new worksheet
in a different workbook (pre-template.) Then, after changing the name of the
troubled worksheet, and the name of the new sheet to the previous name of the
troubled worksheet, I moved it to the workbook. I then copied the
appropriate Columns and Rows (for Headings and Labels, ie, non-formulas) to
the new worksheet. I then copied my first row of formulas from the old
worksheet and did a copy-paste special-formulas to the new worksheet. I then
copied the first row to the used range of my worksheet and I could tell it
was working because the numbers changed appropriately for the different rows.

By the way, I ended up copying from a workbook that had a lot of named
ranges and as it turns out, some of them were the same as range names I was
using in this workbook. But thanks to Name Manager, I was quickly able to
list and then delete only named ranges that referred to external source (the
other workbook)

Thanks Peo and Charles for your suggestions.

 

[BJG2005]

I have an old, large file. I ran into this same problem and
ctrl+alt+shift+F9 fixed it. I seem to need to do this from time to time.
The trick is noticing that it needs to be done.

How can I avoid this? What does it mean?

Thank you, so much, in advance.

Brian

 

[Saruman]

Tools, Options, Calculation Tab, ensure automatic radio button is selected.