Cell value not recalculating (Excel 2000 SR1)

Topic

New Reply

I have a spreadsheet containing the following formula

=NETWORKDAYS(B3,B4,OFFSET(Holidays!A2,0,0,Holidays!A1,1))*0.30625

Sometimes, but not always, this cell doesn’t recalculate when I change the values in cells B3 and B4. (Other cells do!)
Yes, Calculation is set to Automatic. Pressing F9 does nothing. F2 and Enter seems to work – but is a nuisance since the sheet is protected.

This is reminiscent of one of the bugs in XL97 some time ago, but I can’t find any reference to similar problems in 2000.

Can anyone suggest where I might look for an answer? Might it be connected with NETWORKDAYS being in the Analysis Tool Pack?

Thanks

Ian.

Reply | Quote

Replies

It may be due to the analysis toolpack addin.

Why not just add “+ now()*0” to the end of the formula.
It will always recalculate then due to the volatile now() function.

Reply | Quote

No real solution, but you could press control-alt-F9 to force the recalc. Beats having to unprotect…

Reply | Quote

I really don’t know why this is happening, but it could be some corruption on the worksheet. I have a couple of suggestions that might help.

1- Delete the formula and type it back in from scratch. This should rebuild the recalc references list.

2- Save the workbook in HTML format under a new name. Rename the original workbook. Load the HTML file and save it back as the original name. This may solve the problem, but you may also lose some formatting in the workbook.

Reply | Quote

Thanks, everyone, for your suggestions. I can’t immediately test them since the problem is intermittent and hasn’t recurred since yesterday. I’ll post a followup if I find anything more.

Thanks again!

Ian.

Reply | Quote