Removing Data based on a Condition

[ava_h]

I'm not sure what I need to do here, it may be a simple statement. But I need the sums in E3, F3, G3 to automatically update when the status in column H is answered with "deal lost". For example, A5 company is a lost deal, so I need E5 and F5 to be removed from the summed totals in E3 and F3 automatically based on the status of "deal lost". Column H is set up as a drop down menu, so there are only those three choices. I'm using Excel 2007.

 

[mumps]

What do you mean by "update"? What would you like to see happen?

 

[ava_h]

For example, H5 is set to deal lost so I would like for the data in E5, F5, G5 to be removed from the summed calculation in E3, F3, G3. Since the deal was lost, it shouldn't be calculated in the totals of line 3. I would just like for it to automatically adjust the calculation based on the criteria of "deal lost".

 

[mumps]

Place this code into the worksheet code module (not into a regular module). I'm assuming that range E3 to G3 has a formula in it that calculates the totals.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
        If Target = "Deal Lost" Then
            Range("E" & Target.Row & ":" & "G" & Target.Row).ClearContents
        End If
End Sub

 

[ava_h]

Worked perfectly. Thanks!

 

[mumps]

My pleasure.