Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate
I have been sent an EXCEL 2003 file that has "calculate" on the bottom
bar (on thier computer and mine), but is not in manual mode and has been recalculated just to be sure. I will hereaftre refer to this as "the symptom" and that includes the fact that formula auditing does not seem to work either. We have found the following Microsoft note which would seem to explain it (it does have somehwat over the noted amount of dependencies, although I'm not sure what "dependencies to unique referecnes" in the URL below means) http://support.microsoft.com/default.aspx?scid=243495 but there is other evidence that suggests that this note is overstated (or misunderstood by us): The file is only about 5 MB This problem does not occur on other, much larger, interconnected files that have over 100,000 trace depedents and over 100,000 trace precedents In the problem file, if one sheet is completely converted to vlaues, it makes no difference in this symptom. However, if one deletes that same one sheet, the symptom goes away. If one, alternatively, deletes several of the other worksheets, the behavior finally dissapears but it takes a lot of them. Does anyone have any experience with unique characteristics of an EXCEL file that can cause this symptom? It would be maajor pain to try to rebuild the connections to that one major worksheet and who knows if the same symnptoms might come back anyway, once we did. Thanks much! Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate
Dean,
Its not easy to get around this problem. see http://www.decisionmodels.com/calcsecretsf.htm for a more comprehensive explanation. (there are several possible causes) Usually you just have to live with it ... Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "DA" wrote in message ... I have been sent an EXCEL 2003 file that has "calculate" on the bottom bar (on thier computer and mine), but is not in manual mode and has been recalculated just to be sure. I will hereaftre refer to this as "the symptom" and that includes the fact that formula auditing does not seem to work either. We have found the following Microsoft note which would seem to explain it (it does have somehwat over the noted amount of dependencies, although I'm not sure what "dependencies to unique referecnes" in the URL below means) http://support.microsoft.com/default.aspx?scid=243495 but there is other evidence that suggests that this note is overstated (or misunderstood by us): The file is only about 5 MB This problem does not occur on other, much larger, interconnected files that have over 100,000 trace depedents and over 100,000 trace precedents In the problem file, if one sheet is completely converted to vlaues, it makes no difference in this symptom. However, if one deletes that same one sheet, the symptom goes away. If one, alternatively, deletes several of the other worksheets, the behavior finally dissapears but it takes a lot of them. Does anyone have any experience with unique characteristics of an EXCEL file that can cause this symptom? It would be maajor pain to try to rebuild the connections to that one major worksheet and who knows if the same symnptoms might come back anyway, once we did. Thanks much! Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate
From that URL, it seems that the problem is related to a large number
of lookup fucntions, each looking at the same, very wide, range. The recalc time is instantaneous, even though it is apparantly a full recalc. If it is simply a full recalc problem, then the results are correct, right? It also seems that formual auditing is disabled, which is distrurbing to me, as I like to audit the file with those. Thru some tests, the author finds that, apparently, the symptom is triggered when a 5824th exact same lookup referecne is added, so perhaps that clears the 8,000 issue from being the problem and it is more related to the repeated use of large lookup ranges. I do beleive, if we can find the exact cuase, that this file could be reporgrammed to fix the problem. I think the wide ranges were just the easiest way to do it, but that they could be narrowed with not too much trouble. Thanks so much for your response, Charles. Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate % | Excel Worksheet Functions | |||
Calculate the value of Z | Excel Discussion (Misc queries) | |||
calculate time does not calculate | Excel Discussion (Misc queries) | |||
Activesheet.Calculate failing to calculate | Excel Programming | |||
Macro that hide or unhide and not calculate or calculate | Excel Programming |