How to set Calculation Option for one perticular cell as "Manual"?

[pavanbhoyar]

Hello,

I was just wondering whether this can be done.....

I want to set Calculation Option for one particular cell as "Manual" where as rest all the cells in sheet should do calculation "Automatically".

Can this be done?

If yes, Then it will be helpful for me.

Looking for simpler solution.


Thanks in advance.

[TMS]

Not as far as I know. Pretty much all or nothing.

You could perhaps take a copy of the formula and store it in a comment; then convert the formula to a value (Copy and Paste Special | Values).

When you want to recalculate, copy the formula from the comment and paste it back into the formula bar.

The risk is that the formula might change as you add or delete rows. Maybe used (Dynamic) Named Ranges to overcome that potential issue.


Regards, TMS

[joeu2004]

I want to set Calculation Option for one particular cell as "Manual" where as rest all the cells in sheet should do calculation "Automatically". Can this be done?

Not literally. Manual calculation mode is an application-wide state.

If it is truly only one cell, one approach is to move the actual formula somewhere else. Then copy the current value and use paste-special-value to put it where you want it, the cell that contained the formula originally.

Let the actual formula change. That has not effect as long as no formula references it. Copy-and-paste-special-value when you want the change to take effect.

Downside: Since the actual formula might change occassionally, it might have side-effects, depending on the formula design. For example, if the formula takes a very long time to calculate and it references volatile functions or formulas, that will impact overall workbook performance.

------

Alternatively, move the formula to another worksheet.

Then set up the following worksheet macros (right-click on the worksheet tab, then click on View Code).

Sub disableSheet()
Me.EnableCalculation = False
End Sub

Sub enableSheet()
Me.EnableCalculation = True
End Sub

Sub calcSheet()
Me.EnableCalculation = False
Me.EnableCalculation = True
Me.EnableCalculation = False
End Sub

Also set up the following workbook macro (in VBA, press ctrl+R to open the Project Explorer pane, double-click on ThisWorkbook).

Private Sub Workbook_Open()
' change "Sheet1" to the name of the worksheet object that contains the formula.
Sheet1.disableSheet
End Sub

In Excel, you can press alt+F8 and run calcSheet when you want the formula to recalculate.

Note that the workbook must be saved as macro-enable (".xlsm") or in compatibility mode (".xls") to retain the macros.

[pavanbhoyar]

Thank you both of you for your valuable time and response....!!!!!!

[MagicMan]

Perhaps you can set up a control cell with a simple <blank>/yes option then in the cell you want to manually calculate insert an if statement along the lines of:

=if([control cell]="Yes",[Formula],"")

This would only activate the formula if you put "Yes" in the control cell.

[pavanbhoyar]

Thanks for the response...!!!!!