# How to have a cell calculate only once

I have cell `N29` calculating some values based on streaming data. Once a condition is met in cell `Q29`, the calculation or final value should be passed to cell `O29` and no longer calculate in either cell.

For example: If cell `Q29` = y then `O29` should equal the last calculation of `N29` (and no longer calculate) and `N29` should be set to 0 and no longer calculate.

-
Are you open to VBA solutions? – Excellll Jul 17 '12 at 18:13
Yes if need be. Anything is greatly appreaciated. – Marco Jul 17 '12 at 19:42
is there a formula in Q29? If so, does it have to stay a formula, once it reaches 'y'? You might want to move all of the calculation in N29 into a macro and skip testing with Q29 completely, but that means knowing more about your calculation. If you are refreshing data regularly the cell value change event is not possible, but a time delay check can work – datatoo Jul 17 '12 at 20:03
No Q29 doesn't have a formula. Here is what i have: Cell Q29 can either be yes or no. the yes or no will determine the value of N29=IF(OR(AND(G29="s",G32="b",Q29="y",Q32="y"),AND(G29="b",G32="s",Q29="y",Q32="‌​y")),0,((L29*M29)-(I29*M29))/L29) ----and here is the formula for the other cell 0 =IF(N29=0,((L29*M29)-(I29*M29))/L32+((L32*M32)-(I32*M32))/L32) --- I agree that the calculations can be moved to a macro. but how? Thanks! – Marco Jul 17 '12 at 20:16
so are any of these data entries, or are they all streaming, and the calculation triggers on the refresh now? – datatoo Jul 17 '12 at 20:32

Include the following event macro in the worksheet code area:

``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Q29 As Range
Set Q29 = Range("Q29")
If Intersect(Target, Q29) Is Nothing Then Exit Sub
If Q29 <> "y" Then Exit Sub
Application.EnableEvents = False
Range("O29").Value = Range("N29").Value
Range("N29").Value = 0
Application.EnableEvents = True
End Sub
``````

macros must be enabled for this to work!

-