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!

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!

assume E3:O3 has numeric value not equal to zero (0) as a valid entry
then on p3
=if(countif(E3:O3,"=0")=0,SUM(E3:O3),"INC")

"All cells, including O3, are set to a 0 value"
if zero can be a valid entry, the above formula will not work out.

otherwise if u can set E3:O3 to a "blank", a formula can be like this
then on p3
=if(countblank(E3:O3)=0,sum(E3:O3),"INC")

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!

Thanks so much fr a quick reply. However, I just put in some code in VB a
few minutes ago and I am getting a Compile Error: Ambiguous Name error
message. I notice that some of the code I copied to run a different macro in
the same sheet has the same line:

Private Sub Worksheet_Change (ByVal Target As Range).

What do I do so I can work the two macros etc?

The other code for the sheet follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

'sort only if the change was in column P
If Intersect(Target, Me.Range("B3:Q52")) Is Nothing Then Exit Sub

With Me.Range("B3:Q52")
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

THANKS!!


"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!

Combine them (BTW your code doesn't do what the comments say it does)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "O3:O52" '<== change to suit
Const WS_RANGE2 As String = "B3:Q52" '<== change to suit

'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE1)) 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

'sort only if the change was in column P
If Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then

With Me.Range(WS_RANGE2)
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Wayne4js" wrote in message
...
Thanks so much fr a quick reply. However, I just put in some code in VB a
few minutes ago and I am getting a Compile Error: Ambiguous Name error
message. I notice that some of the code I copied to run a different macro
in
the same sheet has the same line:

Private Sub Worksheet_Change (ByVal Target As Range).

What do I do so I can work the two macros etc?

The other code for the sheet follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

'sort only if the change was in column P
If Intersect(Target, Me.Range("B3:Q52")) Is Nothing Then Exit Sub

With Me.Range("B3:Q52")
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

THANKS!!


"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!

Thanks Claude, but this did not work. Here's the situation, I am trying to
get the table to automatically sort after I enter nothing (because the data
is "0"), a 5, or a 10 in the final column (currently column "O"). The
current VB macro sorts the rows based on the score tallied in colum "P" but
the score in col "P" changes as soon as i enter the data in the proceeding
rows, E-O. Once the tally becomes more than one or more of the rows above
the data beig entered, the row sorts up, another row(s) drops, but the cursor
stays in teh same row I was working on before the sort occurs so I end
entering scores for the wrong person.

Bob Phillips has also answered - THANK YOU very much, Bob!!! However, I
have not been able to get his formula to work either. I would be willing to
entertain any suggestions but I have spent way more time than necessary on
this and I am going to back to an earlier version of putting this proram
together that was suggested in another discussion I found early last week
where an 'invisible?' cell is created in the header row and sorts when I
click on the column header. Only sorts the rows with info in them so works
pretty slick. Thanks for your help!!

"claude jerry" wrote:

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!

Thanks Bob, but I spent hours and still could not get this to work. Here's
the situation, I am trying to get the table to automatically sort after I
enter nothing (because the data is "0"), a 5, or a 10 in the final column
(currently column "O"). The current VB macro sorts the rows based on the
score tallied in colum "P" but the score in col "P" changes as soon as I
enter the data in the proceeding rows, E-O. Once the tally becomes more than
one or more of the rows above the data being entered, the row sorts up,
another row(s) drops, but the cursor stays in teh same row I was working on
before the sort occurs so I end entering scores for the wrong person.
Basically, I want the sort to occur after I enter the data in and exit col
"O" so I need to delay teh SUM in col "P" until then. OH ya, I alomost
forgot!! In addition to the other formula I gave you, here is what I have
that sorts the column based on the SUM value in "P".

Option Explicit

Sub SortTable()

'code written by Dave Peterson 2005-10-22
'2006-08-06 updated to accommodate hidden or filtered rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range

TopRow = 2
iCol = 17 '17 columns
strCol = "B" ' column to check for last row

Set curWks = ActiveSheet

With curWks
LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
If Not .AutoFilterMode Then
Set rng = .Range(.Cells(TopRow, strCol), .Cells(LastRow, strCol))
Else
Set rng = .AutoFilter.Range
End If

Set rngF = Nothing
On Error Resume Next
With rng
'visible cells first column of range
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "No visible rows. Please try again."
Exit Sub
Else
FirstRow = rngF(1).Row
End If

myColToSort = .Shapes(Application.Caller).TopLeftCell.Column

Set myTable = .Range("B" & TopRow & ":B" & LastRow).Resize(, iCol)
If .Cells(FirstRow, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If
myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
order1:=mySortOrder, _
header:=xlNo
End With

End Sub


This formula, though, is not in the same area as the one I previously sent
you - it is in "Module1" whereas the other stuff is in "Sheet4 (Code)



THANK YOU very much, Bob!!! I would be willing to entertain any suggestions
but I have spent way more time than I should on this so, in teh meantime, I
am going to back to an earlier version of this proram that was suggested in
another discussion I found early last week. An 'invisible?' cell is created
in the header row and sorts when I click on the column header. Only sorts
the rows with info in them so works pretty slick. Thanks for your help!!

"Bob Phillips" wrote:

Combine them (BTW your code doesn't do what the comments say it does)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "O3:O52" '<== change to suit
Const WS_RANGE2 As String = "B3:Q52" '<== change to suit

'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE1)) 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

'sort only if the change was in column P
If Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then

With Me.Range(WS_RANGE2)
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Wayne4js" wrote in message
...
Thanks so much fr a quick reply. However, I just put in some code in VB a
few minutes ago and I am getting a Compile Error: Ambiguous Name error
message. I notice that some of the code I copied to run a different macro
in
the same sheet has the same line:

Private Sub Worksheet_Change (ByVal Target As Range).

What do I do so I can work the two macros etc?

The other code for the sheet follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

'sort only if the change was in column P
If Intersect(Target, Me.Range("B3:Q52")) Is Nothing Then Exit Sub

With Me.Range("B3:Q52")
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

THANKS!!


"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!