manual calculation range

[thedobbs]

Is it possible to make purely a range of cells calculate manually yet still have the rest of the sheet calculate automatically?

 

[mikerickson]

There is an Automatic except for Tables mode.

 

[SteveO59L]

Dont think so

 

[Richard Schollar]

You can do if this range is a Table (created using the Table formula) otherwise it is probably more trouble than it's worth to implement (and would require code).

Alternatively, you could wrap everything in the table in an IF() statement and only perform the calculations if a certain flag has been set for example:

existing formula:

=VLOOKUP(B5,SomeSheet!$A1:$Z50000,5,0)

formula after IF() wrap:

=IF($A$1=1,VLOOKUP(B5,SomeSheet!$A1:$Z50000,5,0),"Not recalculated")

in which case you determine when you recalculate by changing the value in A1 to 1 (recalculates) or to anything else (formulas return "Not recalculated"). Of course, the problem with this approach is that you get "Not recalculated" for most of the time when you might actually want to see what the previous, unrecalculated value was. If you want this, you need to make a further modification to the formula and also turn Iterations on in Tools>Options>Calculation

assume formula is in C5:

=IF($A$1=1,VLOOKUP(B5,SomeSheet!$A1:$Z50000,5,0),C5)

Note I have explicitly created a circular reference above - this is why you need iterations on.

 

[pgc01]

Hi

Not directly, but you can easily mimic it.

You use an auxilliary worksheet that you can hide. You perform the calculations of that range in the auxilliary worksheet and mirror the cells in the main sheet.

Ex.: You want all cells in Sheet1 to be calculated automatically except for cells F1:F4.

- Insert a new worksheet, for ex. "Sheet1_Calc"

- in Sheet1!F1:

=Sheet1_Calc!F1
Copy to F2:F4

- in Sheet1_Calc!F1:F4 write the formulas needed for the calculations

- disable calculation in Sheet1_Calc

- hide Sheet1_Calc


Now Sheet1 behaves as if all cells are calculated automatically except for F1:F4

To calculate the cells enable/disable calculation in Sheet1_Calc.

 

[Richard Schollar]

I take it you have to do the disabling/enabling of calculations via the Properties window in the VBE? Or is there a shortcut to doing this in the main Excel interface?

 

[pgc01]

Hi Richard

In my case I had a button to calculate the auxilliary worksheet, and so I used vba.

At the beginning I executed in the immediate window:

Worksheets("Sheet1_Calc").EnableCalculation=False

Then to calculate the auxilliary worksheet:

Worksheets("Sheet1_Calc").EnableCalculation=True
Worksheets("Sheet1_Calc").EnableCalculation=False

This is what I had in the button macro.

 

[Richard Schollar]

Understood - thanks

 

[srrboarder]

I'm sorry. I believe pgc01's solution will work for a similar situation I'm having but I don't understand how you're telling the "Sheet1_calc" tab to not update automatically. Where is the option to disable automatic calculations on a specific worksheet (in this case "Sheet1_Calc"? I can find the option to disable this for the entire workbook but not a single worksheet/tab.

Also, I'm in excel 2007.

Thank you for your help!

 

[pgc01]

I'm sorry. I believe pgc01's solution will work for a similar situation I'm having but I don't understand how you're telling the "Sheet1_calc" tab to not update automatically. Where is the option to disable automatic calculations on a specific worksheet (in this case "Sheet1_Calc"? I can find the option to disable this for the entire workbook but not a single worksheet/tab.

Also, I'm in excel 2007.

Thank you for your help!

Hi
Welcome to the board

You have 2 options:

1 - using vba. You create a button and assign it a macro with the 2 statements at the end of post #7.

What they do is simply to enable calculation on Sheet1_Calc (forcing the sheet to recalculate) and then disable calculation on Sheet2_Calc again.

This button you create will work like a "private" F9 key, that forces recalculation but only in that specific Sheet. The rest of the time Sheet1_Calc has calculation disabled.

2 - manually (for ex. for tests)

You change it in the properties window of Sheet1_Calc

To display the properties window

2.1
. Select a cell in Sheet1_Calc
. click on the menu tab Developer
. in the Controls group click on the Properties button

You'll see the Sheet1_Calc properties window and you can change the EnableCalculation property manually.

Notice that this property is specific to Sheet1_Calc, it will not change the calculation in other sheets.

Or

2.2
Go to the VB editor (Alt-F11)
Select the Sheet1_Calc module
In the Menu->View choose Properties Window (or, if you know the button click on the Properties Window button on the toolbar, or press F4)

Now you have the properties window again and you can change the EnableCalculation value.

HTH
.