Stop Formulas updating after a condition set

[P W]

Is it possible (without using VBA / macros) to stop formulas updating / recalculating once a condition is met eg a value in another cell ="finished"?

Eg I have a Price List lookup table containing prices of products. On my data sheet I record orders with a cell having a vlookup function to extract current price of a product from this price lookup table. Once an order is closed (I can set a flag in another cell), I don't want the price to be updated, even if I later update the price list.

 

[bobhc]

Good day P W and welcome to the board if you have not done so, please read this link it will help in the use of the forum.

http://chandoo.org/forum/threads/new-users-please-read.294/

As to your question:-

You could set work book calculations to manual in options/formulas.


.

 

[P W]

Good day P W and welcome to the board if you have not done so, please read this link it will help in the use of the forum.

http://chandoo.org/forum/threads/new-users-please-read.294/

As to your question:-

You could set work book calculations to manual in options/formulas.


.

Thanks for the comments.
There will though always be other formulas that need updating / recalculating - so I don't think this solves my problem?

 

[bobhc]

I think you are going to be forced to go the vba route. You can try this code which has had good reviews. I am not the author.

' shg 2007-0501

Sub CommentFormulas()

' comments out all but array formulas
Dim cell As Excel.Range

With Application
.ScreenUpdating = False
.Calculation = xlManual
For Each cell In ActiveSheet.UsedRange
With cell
If .HasFormula And Not .HasArray Then
.Formula = "'" & .Formula
End If
End With
Next
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Sub UncommentFormulas()
' restores commented formulas
Dim cell As Excel.Range

With Application
.ScreenUpdating = False
.Calculation = xlManual
For Each cell In ActiveSheet.UsedRange
With cell
' the left(cstr(cell)) below accommodates cells having errors
If Left(CStr(.Value), 1) = "=" And Not .HasArray Then
.Formula = .Text
End If
End With
Next
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Comments from a user that may help.
Hi shg,

The commenting function worked great. I thought there was an error (Application Error 1004) but I solved it using the Locals Window. I had an unrelated error in one of the hidden cells, evidenced by my debugging.

Thanks for your help!


.​