refreshing formulas in cells

[Guest]

Hello
I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for
example. For some reason when I change the numbers or add numbers to Cell A1
or B1 the result of the formula dosent change in Cell A2 unless I page down
and page up and then It shows the new total. Ive used formulas for years and
every time I made a change to a number you could see the result in the cell
containing the Sum formula right away even though I was in the same frame of
that worksheet. My Auto calculate is checked vs using manual calculation
under Tools>Options. Im using Office 2000.
What causes this?

 

[Dave Peterson]

I don't really have a guess, but if you start a new workbook and try this, does
it suffer from the same problem?

If not, how about trying this against the troublesome workbook.

Ctrl-a (twice in xl2003) to select all the cells
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Maybe this will wake up excel's calculation engine by forcing it to reevaluate
all your formulas.

(maybe not, though...)

 

[Dave Peterson]

Ps. I've never seen anything like this, but there have a few posts that said
this sometimes helps.

 

[Guest]

I have also had this problem and never saw anything like it before. In my
situation, creating a new file did not prevent the problem. Also, it seems
to respond to Save As or deleting blank columns. Then it magically shows the
new values.

The strange thing is it starts refreshing all cells and then stops. The
base calcs change and some of the vlookups referencing them change. It then
freezes. In some cases, you'll see the same number repeated on each row (the
one it becomes stuck on gets repeated).

Once unstuck the file works fine until you add new content, then it freezes
again and I repeat the steps above.

I assume this is a problem in 2003 and am concerned that any complicated
analysis is unstable due to this problem. Has this been identified? Are
they working on it?

Ps. I've never seen anything like this, but there have a few posts that said
this sometimes helps.

 

[Niek Otten]

Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9

--
Kind regards,

Niek Otten

I have also had this problem and never saw anything like it before. In my
situation, creating a new file did not prevent the problem. Also, it
seems
to respond to Save As or deleting blank columns. Then it magically shows
the
new values.

The strange thing is it starts refreshing all cells and then stops. The
base calcs change and some of the vlookups referencing them change. It
then
freezes. In some cases, you'll see the same number repeated on each row
(the
one it becomes stuck on gets repeated).

Once unstuck the file works fine until you add new content, then it
freezes
again and I repeat the steps above.

I assume this is a problem in 2003 and am concerned that any complicated
analysis is unstable due to this problem. Has this been identified? Are
they working on it?

Ps. I've never seen anything like this, but there have a few posts that
said
this sometimes helps.

 

[Guest]

What do you mean by rebuilding the dependency tree? Is this a way to
jumpstart the f9 key?

Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9

--
Kind regards,

Niek Otten

I have also had this problem and never saw anything like it before. In my
situation, creating a new file did not prevent the problem. Also, it
seems
to respond to Save As or deleting blank columns. Then it magically shows
the
new values.

The strange thing is it starts refreshing all cells and then stops. The
base calcs change and some of the vlookups referencing them change. It
then
freezes. In some cases, you'll see the same number repeated on each row
(the
one it becomes stuck on gets repeated).

Once unstuck the file works fine until you add new content, then it
freezes
again and I repeat the steps above.

I assume this is a problem in 2003 and am concerned that any complicated
analysis is unstable due to this problem. Has this been identified? Are
they working on it?

 

[tony h]

I have seen a similar problem (is it the same one) If you have
userdefined functions referenced from the worksheet. If the userdefined
functions do not have error trapping in them (on error goto etc) then if
such an untrapped error arises it stops all calculation from that point
on without warning.

They can be really difficult to track down. So put error trapping in
all functions.

 

[Niek Otten]

Very, very rarely, Excel loses track of which cells depend on which others
(the "dependency tree").
This key combination (available in later versions of Excel, I don't know
exactly from which one) analyses the workbook from scratch and rebuilds the
dependency tree.
Several successful tries have been reported in these newsgroups.

--
Kind regards,

Niek Otten

What do you mean by rebuilding the dependency tree? Is this a way to
jumpstart the f9 key?

Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9

 

[Niek Otten]

Depending on the version of Excel, even with error trapping you can
experience this problem in at least one version of Excel.
I have a few workbooks which I never managed to run correctly in Excel97.

 

[Guest]

No user defined functions, just lots of repetitions of the basic ones. I
pulled up a version of the file that I saved stuck repeating the wrong
vlookup value in several rows.

The Ctrl+Alt+Shft+F9 did cause the file to refresh (correctly). This seems
to work. Will I need to do this everytime I make major changes? I am
concerned that it may not always be so obvious that Excel is not refreshing.

Thanks!

 

[Niek Otten]

<The Ctrl+Alt+Shft+F9 did cause the file to refresh (correctly). >

Fine!!