-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Looking for advice on preventing a range from calculating.
- Thread starter atf32
- Start date
-
- Tags
- calcuated field
Hi
If you mean inserted in a worksheet that has calculation enabled, no.
There is, however, a simple workaround that will mimic what you want.
You can mirror the values of an auxilliary worksheet (that you can hide) where you place all the formulas of the range that you want to only calculate on demand.
Disable calculation in the auxilliary worksheet. This way the values in the range will only change when you force calculation in the auxilliary worksheet.
This will work as if you have a range in the worksheet that is frozen. All the cells around will update automatically as usual.
If you mean inserted in a worksheet that has calculation enabled, no.
There is, however, a simple workaround that will mimic what you want.
You can mirror the values of an auxilliary worksheet (that you can hide) where you place all the formulas of the range that you want to only calculate on demand.
Disable calculation in the auxilliary worksheet. This way the values in the range will only change when you force calculation in the auxilliary worksheet.
This will work as if you have a range in the worksheet that is frozen. All the cells around will update automatically as usual.
Upvote
0
shg
MrExcel MVP
- Joined
- May 7, 2008
- Messages
- 21,845
- Office Version
- 2010
- Platform
- Windows
If I understand pgc's suggestion, it requires VBA.
For a simple example, consier a workbook with two sheets, Sheet1 and Sheet2.
In Sheet2!A1, enter =RAND().
In Sheet1!A1, enter =Sheet2!A1
Press F9 and watch Sheet1A1 -- it recalculates, as you wold expect.
In the VBA Immediate window, enter Sheet2.EnableCalculation=False
Now pressing F9 has no effect.
To recalculate Sheet2, Sheet2.EnableCalculation=True, then back to False
For a simple example, consier a workbook with two sheets, Sheet1 and Sheet2.
In Sheet2!A1, enter =RAND().
In Sheet1!A1, enter =Sheet2!A1
Press F9 and watch Sheet1A1 -- it recalculates, as you wold expect.
In the VBA Immediate window, enter Sheet2.EnableCalculation=False
Now pressing F9 has no effect.
To recalculate Sheet2, Sheet2.EnableCalculation=True, then back to False
Upvote
0
??
With my solution the workbook is in calc mode.
Maybe it was not clear. It is only the auxilliary worksheet (that can be hidden) that is not calculating automatically. All the other sheets in the workbook are calculating automatically.
Upvote
0
Thanks shg
That was what I meant.
The vba solution is handy, for ex. you can use a button to calculate.
It's also possible to do it directly:
- in the ribbon->developper in the properties of the sheet enable, disable calculation
or
- right-click on the worksheet tab, View code, and again in the properties of the sheet enable, disable calculation
That was what I meant.
The vba solution is handy, for ex. you can use a button to calculate.
It's also possible to do it directly:
- in the ribbon->developper in the properties of the sheet enable, disable calculation
or
- right-click on the worksheet tab, View code, and again in the properties of the sheet enable, disable calculation
Upvote
0
Similar threads
- Question
- Question
- Question
