A little advice on comparing cells

[systematic]

Hello all,

I am making a worksheet for staff members to track how they are
performing.

Column A contains timeslots in 15 mins intervals, Column B contains the
volume of transactions they should aim to complete by that timeslot.

Column E has the number 1-150 (transactions), then Column F is where
they will indicate they have completed a transaction (by typing a '1'
in the cell).

What I would like to do is use the worksheet to tell them if they are
on target.

I'm thinking it could work something like this -

When they indicate a transaction is complete, use a change event to
copy and paste NOW() value in adjacent cell (so that is does not
update).

Then lookup the timeslot in A:A that <= NOW(). Compare the total
transactions completed up until NOW() to the value in the list and then
be able to return a value (could be any value) based on whether the
number of transactions completed is > or < what is in the list.

I'm not sure about the formula to do this - and was hoping someone
could help me out.

Thanks

Rob

 

[jtp]

I think I have an understanding of what your looking for. I'm by n
means a pro at Excel but, this does work. I put the time stamp i
column F (basically beside the 1) and the current +/- goal in column G
It subtracts the persons actual transactions in column E from the goa
in column B. Sums them all up to show if they are over/under at th
current time. Place the code in the current worksheet object.

-Private updated As Boolean, thisTime As Date

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Column = 6 And Target.Value = 1 Then
thisTime = Right(Now(), (Len(Now()) - InStr(Now(), " ")))
updated = True
Target.Offset(0, 1).Value = thisTime
ElseIf updated = True Then
Target.Offset(0, 1).Value = CompareColumns(thisTime)
End If

End Sub

Private Function CompareColumns(thisTime As Date)
Dim cell As Range, totalCounts%

'Now start comparing
Set cell = Range("A2")

While cell.Value <= thisTime And cell.Value <> ""
CompareColumns = CompareColumns + (cell.Offset(0, 4).Value
cell.Offset(0, 1).Value)
Set cell = cell.Offset(1, 0)
Wend
updated = False
End Function-

Without a doubt there is a better way to do it but this was the quic
and easy way. Hope this helps and good luck,

Jaso