Removing Data based on a Condition

ava_h

New Member
Joined
Oct 29, 2010
Messages
23
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.


Picture1.png
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,121
What do you mean by "update"? What would you like to see happen?
 

ava_h

New Member
Joined
Oct 29, 2010
Messages
23
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,121
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top