Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
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!

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .