Woorksheet Calculate after every entry
Posted by Leslie on March 08, 2001 12:41 PM
Why does my worksheet calculate after every entry?
It didn't before. I know there might be a macro somewhere, but how do I find it and turn it off?
Thanks
Posted by Wayneo on March 08, 2001 12:53 PM
Leslie,
One way to avoid this problem is to go to "Tools"
on the Menu bar....then select "Options"...then "Calculation". Select "Manual" and this will take care of the probelm. When you want the sheet to calculate, select "F9". Hope this helps
Posted by Aladin Akyurek on March 08, 2001 1:22 PM
One possibility is that your workbook contains named dynamic ranges. You can see them via Insert|Define|Name. It seems Excel has to check whether or not one of these ranges expanded AFTER ANY ENTRY ANYWHERE. One solution is, I guess, to use a faster computer.
Aladin
Posted by Mark W. on March 08, 2001 1:55 PM
Are you using a volatile function? What are the
functions that you're using?
Posted by Aladin Akyurek on March 08, 2001 2:26 PM
Mark: If the answer is yes (because of the use of OFFSET, INDIRECT, INDEX, etc.), what is the solution, save the manual calc or a faster computer?
Aladin
Posted by Mark W. on March 08, 2001 2:39 PM
Aladin, I don't believe OFFSET, INDIRECT, INDEX
are volatile. RAND and NOW are... and user-defined
functions that are explicitly set as volatile.
F2+Enter will cause this function to be re-evaluated.
Posted by Aladin Akyurek on March 08, 2001 2:56 PM
Mark: Maybe they are in Excel 97. Anycase, I've recently built a model that has a few named dynamic ranges (OFFSET) and INDIRECTs. etc., but no UDF's. Excel now keeps recalculating after any entry anywhere followed by just Enter. I suspect dynamic ranges to be the main reason for recalculations.
Posted by Mark W. on March 08, 2001 2:57 PM
Also...
From the Using the CALL and REGISTER Function help
topic:
"On a worksheet, you can make a DLL function
or code resource volatile, which means that it
recalculates every time the worksheet recalculates."
Posted by Mark W. on March 08, 2001 3:02 PM
Do any of the cells within these ranges have
a volatile function? I can make =1+B1 volatile
just by entering =RAND()*0 into cell B1.
Posted by Mark W. on March 08, 2001 3:12 PM
Here's the poop!
See http://support.microsoft.com/support/kb/articles/Q248/1/79.ASP
Functions that are recalculated automatically when
data in the worksheet changes are called volatile
functions. The following functions are volatile:
AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()
Posted by Mark W. on March 08, 2001 3:21 PM
One approach...
> what is the solution, save the manual calc or a
> faster computer?
One approach would be to move them to another
workbook that can remain closed and reference
these values externally.
Posted by Aladin Akyurek on March 08, 2001 3:45 PM
No. Just OFFSET, used to create named dynamic ranges via Insert|Name|Define, plus dynamically computed addresses of ranges which then figure in various formulas, wrapped into INDIRECT (eg INDIRECT($B$2) where B2 contains the address of a range, say, $A$1:$A$12). I use the latter for computations that are local/restricted to a single sheet.
