Any way to disable calculations for specific cells while others are automatic?

[Harry0]

Obviously this is for efficiency and resource frieendly use.
Basically to have a formula in a specific cell to only calculate if it is passed a certain day and keep its past calculation if past the certain day, despite its referenced cell changes.

The only way it seems now is to copy and past special value, which can take up time and not usable for people that don't know.
One post is kind of similar which seems like only a VBA method can be kind of done.
http://chandoo.org/forum/threads/disable-worksheet-calculation.7251/#post-41683

 

[Luke M]

With individual cells in a worksheet, no. You can individially toggle worksheets though, in a workbook. Check out the sheet properties window:


NOTE: Generally, this is frowned upon as it can lead to erratic behavior and confusing results. Long calculation time is often a symptom of bad formulas, and should be corrected rather than "worked around".

 

[jeffreyweir]

Harry, further to what Luke said, this would merely address the symptoms, while effectively sweeping the root cause under the carpet. You need to address the fundamental design problems in your file. Again I refer you to the draft blog post I provided a link for at http://forum.chandoo.org/threads/excel-can-not-handle-it.20333/#post-122938

Setting calculation to manual - or replacing formulas with values - is dangerous. The only option you have is to set calculation for the entire file to manual. I’d never set calculation to manual if I could help it. There’s just too much chance that someone someday will use output of such a model without remembering to set calculation to Auto. What’s worse, when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened. I have seen many cases in my career where analysts have done just that…opened a workbook with calc set to manual, opened a whole bunch of others where calc was set to auto, and then done an entire day’s work without realizing that calculation was subsequently turned off for all of them.

 

[NARAYANK991]

Hi ,

Much as I support all of what Luke and Jeff have said , I'd like to correct Luke on one point.

You can use VBA , specifically the Range.Calculate method , to recalculate just one cell , even while the entire workbook is in Manual Recalculation mode.

All this is in case you know what you are doing , being fully aware of all the implications.

To illustrate this , put the Recalculation mode to Manual and enter any IF formula , such as =IF(A1>10 , "Greater than 10" , "Less than 10") in cell B1. Initially , if A1 is blank , B1 will display Less than 10. Enter the value 11 in A1 ; B1 continues to display the same text message.

In the Immediate window of the VBE , type in :

[B1].Calculate

The text message in B1 should change to Greater than 10. No other formula in the workbook will be recalculated.

Narayan

 

[Luke M]

Thanks for the clarification, Narayan. I was thinking of the toggles, but you are right, you can recalculate just a range of cells.

 

[Harry0]

Would this be easier.

How about having a date added formula?
If A1 is added today, have B1 state 11/21/14
If A2 is added tomorrow, have B2 state 11/22/14 and B1 will be the same to be on the 21st since that is when it was added.
And so on.

Or to take it a step forward if A1 is changed in 2 days have B1 state 11/23/14, while B2 is still the same at 11/22/14.

Windows keeps the file date when added, without daily processing being done for every file, so why not have this be as consistent in the options?

On a site note again
If the user is to blame and not the tools then the first user to blame is MS developers that programed how Excel works, since the tools to make it more user friendly is there. But then again you play the hand you are dealt with, then that consists of many people that fail and one that wins.