Excel Manual Calculation

[Brandon]

I am an advanced excel user. I have used excel on a daily basis for over 15
years now. I work in accounting. Recently in the past year I have been an
excel error that I have never seen before.

It occurs when I am pasting information from one excel sheet into another.
I will paste into a column of numbers in the destination spreadsheet and the
calculation notifier will appear in the bottom left hand of my screen as if I
had the worksheet set to "manual" calculation. However, I have it in
automatic calculation. Even when this error appears, if I go to
Tool/Options, the spreadsheet will show that I have the calculation set to
Automatic. If I hit F9, the calculation will not update. For example if I
pasted $140 in cell A1, and $200 in cell A2, the calculation in cell A3
=SUM(A1:A2) would give a zero result. "calculation" would appear in the
bottom left hand screen, and hitting F9 does not accomplish anything. The
only way to correct this issue would be to shut down office completely.
Reopen the spreadsheet and the calculation appears to have resolved itself.

A few other things that I have observed:

- This appears to happen randomly. Without a particular event occuring to
cause it. For the past year, it happens 2 - 3 times a month.

- I have even gone as far to doing a reinstall of the image on my machine to
correct this and it still occurs.

- It has only been happening to me. Its not happening to other users in our
group.

-Which leads me to believe that perhaps its a function that I am using that
others are not. Perhaps its because I always use the "Ctrl" keys to copy and
paste. Perhaps its because I leave my computer in standby overnight when
others do not. Perhaps its because I utilize software that is open in the
background that others do not have installed on their computer.

-For example, it just happened with a spreadsheet that an employee emailed
to me. I showed the employee the calculation error. He stated that he never
experience the issue on his computer. We have the same version of excel
installed. Excel 2003 (11.8169.8172) SP 3.

I found article 950340 which seems to discuss the error I am having but it
was for service pack 2. I have service pack 3 so it should have been
corrected. The other issue is that everyone else in my group has the same
version, but are not getting the issue. It leads me to believe I am using
some functionality that others are not utilizing which is the catalyst for
this "bug". In the 15 years I have been using excel I have never seen
anything like this until this past year.

 

[Pete_UK]

This link explains four things that will cause the "Calculate" message
to be seen on the status bar:

http://www.decisionmodels.com/calcsecretsf.htm

Hope this helps.

Pete

 

[Brandon]

I am not sure about reason 4.

Reason 1 - As obviously stated in my post. I do not have the calculation set
to manual.
Reason 2 - I don't have the calculation set to manual, nor do I have
circular references.
Reason 3 - I am not using Excel 2000.
Reason 4 - I am not absolutely certain about the dependancy limits.
However, one thing is with the workbook I just received that caused the
error. I received this workbook from another employee who is not
experiencing this error.

So is this dependancy limit based on the workbook it self? If so, then I
would think that the others that use this workbook would be getting this
error. Is it based on the amount of files that I have open at one time.
This morning it happened to me with 1 excel workbook open and 3 word
documents. I don't believe that would be causing it, since its not alot of
memory required. Or could it be happening because often leave my computer on
overnight. The last time I shut it down was yesterday. Could these
dependancy limits keep piling up on my memory even though I do not still have
the files open?

 

[George Nicholson]

Could these

dependancy limits keep piling up on my memory even though I do not still
have
the files open?

You mean: could you possibly be experiencing a memory leak that accumulates
into something significant over a period of days? Sure.

 

[Brandon]

George,

My question was two fold. You did answer part of it on whether or not
memory issues could accumulate over a number days. The other part was how
these "dependancy limits" specifically are treated in memory and whether or
not they accumulate in memory after the worksheet is closed. The dependancy
limit point in Pete's link (http://www.decisionmodels.com/calcsecretsf.htm)
is a bit hard to interpret. For example it says:

....The number of different areas in a sheet that may have dependencies is
limited to 65,536.

This morning I had only 1 excel workbook open, and three word documents.
The excel work book utlized less than 100 rows and only a few columns.
Nowhere near that 65K limit. Even the workbooks that I have used over the
past few days are very small and I have a brand new laptop with over 2 gbs of
memory.

So my question revolves specifically on whether or not the "dependancy
limits" are accumulated in memory. For example, are they only based on
workbooks that you have currently open?

I am trying to get to the bottom of this issue, since the work I do is
extremely sensitive and I want to ensure that there are no errors in the
reporting I do. Also, I don't see my IT department upgrading everyone to
excel 2007 anytime soon. So if it is this dependancy limit issue, i want to
be able to understand how it is happening in my relatively small
spreadsheets. The workbook that I was working in this morning was only 95kb.