Calculate Sum in one cell after Exiting (ENTER or TAB) another
If i Got you correctly < this should solve your query, in cell P3 enter
=IF(O3="",0,SUM(E3:O3)) and drag the formula to all cells below it
Then you sort and format as you wish
claude
"Bob Phillips" wrote:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O3:O52" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 1).Value = Application.Sum(.Offset(0, -10).Resize(1,
11))
Me.Range("E3:P52").Sort key1:=Me.Range("P3"), _
order1:=xlDescending, _
header:=xlNo
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Wayne4js" wrote in message
...
How can I calculate the sum of a row (E3:O3) in cell P3 ONLY after I enter
the value in the last cell in the row, O3? I want to enter values in the
cells E3:O3 but don't want the sum to calculate in P3 until after I exit
cell
O3.
Other notes:
1. All cells, including O3, are set to a 0 value
2. There are 50 rows, E3:E52
3. After the value in P3 is calculated, a sort occurs moving the data from
the row based on value (100 max) in descending order.
ANY HELP WOULD BE GREATLY APPRECIATED!! I have been searching discussion
pages, office help etc for hours and nothing. Guess I don't have the
terminology down yet!
|