Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
show 5 more comments

1 Answer 1

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.