Hello,
Yea, you see what I want but anyway, let me be more
specific by answering your questions.
Lookup Table:
JobID SLAMAX
A1 <48:00
A11 <48:00
A23 <48:00
A14 <8:00
A15 <8:00
- The range should go something like above. This is just
an example on how is should look. Certain JobID has to
meet the same SLAMAX.
Data Table
JobID TimeRec TimeSolved TimeTaken SLACompliance
A1 9:00am 12:30pm 3:30 Good Job
A11 8:45am 12:00pm 3:15 Good Job
A15 9:30am 6:00pm 8:30 Not Complited
Answers to your question:
q1: The JobID is a string. Combination with letters and
numbers.
q2: The range of the Lookup Table is placed in the same
table but the columns will be hidden. It can be defined
as any name which is meaningful e.g. SLAMAX
q3: At the moment, the time is stored as a timeserial and
moreover I had created a formula to compare the time
(=TEXT(F3-E3,"h:mm").
q4: Yeah, I would rather use a formula in the worksheet.
I don't need a VBA function.
Thanks a lot for your help.
Sheela
-----Original Message-----
Sheela
Guessing:
Your decode table is SORTED and looks like:
LookupTabel
JobID SLAMax
0 24:00
15000 48:00
23000 18:00
DataTabel
JobID TimeRec TimeSolved TotalHours
SLACompliance
23 13-10 14:00 14-10 08:30 18:30
TRUE
15310 13-10 14:00 14-10 08:30 18:30
TRUE
23050 13-10 14:00 14-10 08:30 18:30
FALSE
Lookup Table must be sorted on JobID
SLAMax/TotalHours is excel timeserial with [h]:mm format
SLACompl formula
=D9<=VLOOKUP(A9;$A$2:$B$5;2)
If this wont work for you..why dont you be more SPECIFIC?
q1: What does the JobID look like? is it a string or a
number?
q2: How and where have you defined the table to decode
the jobID to
xWDrange
q3: How are your times stored? As Excel Data/TimeSerials
q4: You are automating.. does that mean you want a
Formula, or do you
NEED a VBA function. (Probably not needed, , more
cumbersome , and
slower)
Suc6
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage:
http://members.chello.nl/keepitcool >
Hi Tom,
Actually, I'm trying to automate an excel spreadsheet.
It
tracks the time taken by the employee to complete a
task
depending on what the job is. I had given a JobId for
each job and I have grouped the common job which needs
to
be completed within a certain time frame.
I have posted this question before, but I had made
some
changes.
There are 5 columns. I had created the formula in
TotalHours where it calculates the time taken to
complete
a task. Each task is given a JobId. Its based from a
standard table given to me to refer. I have also
create
in a separate column the list of JobIds and the task
associated to it. I had named the range of cells.
The SLACompliance checks the JobID and refers the
range
to see which range it falls.
The condition of the range:
If the JobId falls in the twoworkingday range, the
time
to be taken to complete a task is not more than 48
hours.
ElseIf the JobId falls in the eighthours range, the
time
to be taken to complete a task is not more than 8
hours.
ElseIf the JobId falls in the fourworkingday range,
the
time to be taken to complete a task is not more than
96
hours.
ElseIf the JobId falls in the fiveworkingday range,
the
time to be taken to complete a task is not more than
120
hours.
I hope this is not so confusing to you.
Your help is very much appreciated.
Thanks.
-----Original Message-----
If JobID = Range("twoworkingday") Then
Twoworkingday should refer to a single cell.
You never use SLAComp - not sure why you have the code.
--
Regards,
Tom Ogilvy
Hello,
I need some help to write a statement in the code
below:
------------------------
Private Function VLookup(JobID, TotalHours)
SLAComp = "No"
If JobID = "twoworkingday" Then
If TotalHours < 48 Then
SLAComp = "Yes"
End If
ElseIf TotalHours < 8 Then
SLAComp = "Yes"
End If
VLookup = SLACompliance(JobID, TotalHours)
End Function
--------------------------
"twoworkingday" is the name of a range of cell from
another worksheet. How do I write the code in the
line "If JobID = "twoworkingday" Then"?
Thanks for your assistance.
Sheela
.
.
