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!