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

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

Sheela wrote in message
...
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

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.

JobID TimeRec TimeSolved TotalHours SLACompliance
-------------------------------------------------------

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

Sheela wrote in message
...
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


.

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


"Sheela" wrote:

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

Sheela wrote in message
...
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


.

Pardon me, but don't you get a conflict between your function and the
spreadsheet function Vlookup? Do you only ever call your function from
within VBA?

Geoff



"Sheela" wrote in message
...
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

Well, I'm trying two different ways. I believe using the
worksheet function, it'll be easier. I had tried many
ways but to no avail. I need an idea.

sheela


-----Original Message-----
Pardon me, but don't you get a conflict between your
function and the
spreadsheet function Vlookup? Do you only ever call your
function from
within VBA?

Geoff



"Sheela" wrote in message
...
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


.

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


"Sheela" wrote:

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

Sheela wrote in message
.. .
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


.



.

Sheela,

well my formula wasn't a bad guess then :)

except you have the clear the "<" from the SLAMAX
so it's a valid time. (format as [h]:mm)

you'll get NA for job not found...
use that for conditional formatting to highlight errors.

e9 =choose(d9=<vlookup(a9,lookupTbl,2,0)+1,"Not","Goo d")

or make it into -1;0;1 (error,false,true) and use a custom number format
for the text formatting

=IF(ISERROR(VLOOKUP(A9,LookupTbl,1,0)),-1,N(D9<=VLOOKUP
(A9,LookupTbl,2,0)))

with numberformat "Good",[blue]"Invalid JobID",[red]"Non compliance"


cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sheela" wrote:

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


"Sheela" wrote:

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

Sheela wrote in message
. ..
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


.



.

Hi KeepITCool,

Well, I have tried this formula:

=IF(ISERROR(VLOOKUP(B4,SLAMAX,1,0)),-1,N(G4<=VLOOKUP
(B4,SLAMAX,2,0)))

But for any jobid that I key in, it only shows 0 in
SLACompliance column.

JobID TimeReceived TimeSolved TotalHours SLACompliance
A11 9:00 AM 12:00PM 3:00 0
A63 10:00 AM 12:00PM 2:00 0
10:00 AM 1:00PM 3:00 -1

The lookup range is placed between column JobID and
TimeReceived but it'll be hidden later. It looks like
this:

A13 8
A14 8
A15 8
A4 8
A5 8
A61 8
A65 8
A71 8
A72 8
A11 48
A12 48
A16 48
A2 48
A3 48
A63 48
A64 48
A8 48
A62 96
A121 120


Anyway, what is the N function for in the formula?

Thanks,

Sheela

-----Original Message-----
Sheela,

well my formula wasn't a bad guess then :)

except you have the clear the "<" from the SLAMAX
so it's a valid time. (format as [h]:mm)

you'll get NA for job not found...
use that for conditional formatting to highlight errors.

e9 =choose(d9=<vlookup(a9,lookupTbl,2,0)+1,"Not","Goo d")

or make it into -1;0;1 (error,false,true) and use a
custom number format
for the text formatting

=IF(ISERROR(VLOOKUP(A9,LookupTbl,1,0)),-1,N(D9<=VLOOKUP
(A9,LookupTbl,2,0)))

with numberformat "Good",[blue]"Invalid JobID",[red]"Non
compliance"


cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sheela" wrote:

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


"Sheela" wrote:

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

Sheela wrote in message
.. .
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


.



.



.

Sheela...

:(

I hope the SLAMAX are TIMESERIALS not integers!
enter the number 2 then format like [h]:mm
and you'll get 48:00

like your 3:30 is in fact the number 0,145833333
so 48:00 is the number 2

this however appears not to be the only problem...


are you sure SLAMAX name refers to a 2 column range?
are you sure the JobID is in columnB
are you sure the TotalHours are in column G


what the N function does... ?? Please try HELP!
(in this case it'll convert true and false to numbers 1 and 0)


IF the SALAMX is typed (or already looked up)
in column C.. wouldn't it be a little simpler to just
compare Totalhours to that hidden column slamax??????


if need be ... email the workbook to address below.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sheela" wrote:

Hi KeepITCool,

Well, I have tried this formula:

=IF(ISERROR(VLOOKUP(B4,SLAMAX,1,0)),-1,N(G4<=VLOOKUP
(B4,SLAMAX,2,0)))

But for any jobid that I key in, it only shows 0 in
SLACompliance column.

JobID TimeReceived TimeSolved TotalHours SLACompliance
A11 9:00 AM 12:00PM 3:00 0
A63 10:00 AM 12:00PM 2:00 0
10:00 AM 1:00PM 3:00 -1

The lookup range is placed between column JobID and
TimeReceived but it'll be hidden later. It looks like
this:

A13 8
A14 8
A15 8
A4 8
A5 8
A61 8
A65 8
A71 8
A72 8
A11 48
A12 48
A16 48
A2 48
A3 48
A63 48
A64 48
A8 48
A62 96
A121 120


Anyway, what is the N function for in the formula?

Thanks,

Sheela

-----Original Message-----
Sheela,

well my formula wasn't a bad guess then :)

except you have the clear the "<" from the SLAMAX
so it's a valid time. (format as [h]:mm)

you'll get NA for job not found...
use that for conditional formatting to highlight errors.

e9 =choose(d9=<vlookup(a9,lookupTbl,2,0)+1,"Not","Goo d")

or make it into -1;0;1 (error,false,true) and use a
custom number format
for the text formatting

=IF(ISERROR(VLOOKUP(A9,LookupTbl,1,0)),-1,N(D9<=VLOOKUP
(A9,LookupTbl,2,0)))

with numberformat "Good",[blue]"Invalid JobID",[red]"Non
compliance"


cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sheela" wrote:

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


"Sheela" wrote:

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

Sheela wrote in message
. ..
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


.



.



.