Hi
I am makeing a worksheet for the dispatchers ast works.
This is what I did so far.
TECH NAME JOB# TIME HHHC STATUS
LUIS G 1R 8 - 11am fail Completed
3R 11 - 2pm
5R 2 - 5PM
Tech name, time, HHHC and status have a drop down list. their reference is
in cell AA.

So what I need the dispatch to manualy type is the Job#, anything else is a
drop down list or combo.
I need to have in sheet2 a report of all the jobs that still not completed
according to their time scheduled.. For example:

TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
LUIS G 11 - 2PM 1 2
2 - 5PM 1


My boss wants to know how many jobs still not completed by timeframe and the
technician responsible for that open job. It will be a report that I have to
email several times a day.

I need your help ASAP if possible.
Thank you

if the Tech Name is repeated in every row, then you only need to use an
AutoFilter selecting null in the HHHC column

"OscarE" <(E-Mail Removed)> wrote in message
news58D5F64-EF7B-4565-AF16-(E-Mail Removed)...
> Hi
> I am makeing a worksheet for the dispatchers ast works.
> This is what I did so far.
> TECH NAME JOB# TIME HHHC STATUS
> LUIS G 1R 8 - 11am fail Completed
> 3R 11 - 2pm
> 5R 2 - 5PM
> Tech name, time, HHHC and status have a drop down list. their reference is
> in cell AA.
>
> So what I need the dispatch to manualy type is the Job#, anything else is
> a
> drop down list or combo.
> I need to have in sheet2 a report of all the jobs that still not completed
> according to their time scheduled.. For example:
>
> TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
> LUIS G 11 - 2PM 1 2
> 2 - 5PM 1
>
>
> My boss wants to know how many jobs still not completed by timeframe and
> the
> technician responsible for that open job. It will be a report that I have
> to
> email several times a day.
>
> I need your help ASAP if possible.
> Thank you
>
>

The Technician name will change. There are 22 technicians , and each will get
a route of 10 jobs daily. I need to send a report to my boss of who is not
closing their jobs ontime. For example if a technician has 4 jobs from 8 -
11am and is already 10 am , let's say he has completed 2 jobs . He is not
gonna make it ontime and my boss wants to know that.
thanks

"Patrick Molloy" wrote:

> if the Tech Name is repeated in every row, then you only need to use an
> AutoFilter selecting null in the HHHC column
>
> "OscarE" <(E-Mail Removed)> wrote in message
> news58D5F64-EF7B-4565-AF16-(E-Mail Removed)...
> > Hi
> > I am makeing a worksheet for the dispatchers ast works.
> > This is what I did so far.
> > TECH NAME JOB# TIME HHHC STATUS
> > LUIS G 1R 8 - 11am fail Completed
> > 3R 11 - 2pm
> > 5R 2 - 5PM
> > Tech name, time, HHHC and status have a drop down list. their reference is
> > in cell AA.
> >
> > So what I need the dispatch to manualy type is the Job#, anything else is
> > a
> > drop down list or combo.
> > I need to have in sheet2 a report of all the jobs that still not completed
> > according to their time scheduled.. For example:
> >
> > TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
> > LUIS G 11 - 2PM 1 2
> > 2 - 5PM 1
> >
> >
> > My boss wants to know how many jobs still not completed by timeframe and
> > the
> > technician responsible for that open job. It will be a report that I have
> > to
> > email several times a day.
> >
> > I need your help ASAP if possible.
> > Thank you
> >
> >

so your table of jobs is Sheet1 and the output will go to sheet2

copy this into a standard code module

Option Explicit

Sub GetReport()
Dim rowindex As Long
Dim worker As String
Dim rw As Long

'clear target & add headers
With Worksheets("Sheet2")
.Cells.Clear
.Range("A1") = Range("A1")
.Range("B1") = Range("C1")
End With
rowindex = 1

'check jobs
For rw = 2 To Range("B1").End(xlDown).Row
If Cells(rw, 1) <> "" Then
worker = Cells(rw, 1)
End If
'check status
If Cells(rw, "E") <> "Completed" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = Cells(rw, 3)
End With
End If
Next

Summarize

End Sub

Sub Summarize()
With Worksheets("sheet2")
.Range("E1") = .Range("A1")
.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E1"), _
Unique:=True
If .Range("e3") <> "" Then
.Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub

it checks the Status column, if the word Completed isn't there, then the
workers name and job time gets copied to the report ( this is sheet2)
when all the names are read, the summary is added.

To add a code module, go to the development environment, ALT+F11, then from
the menu choose INSERT then MODULE

this should be enough to get you going. Let me know what else we can help
with





"OscarE" <(E-Mail Removed)> wrote in message
news58D5F64-EF7B-4565-AF16-(E-Mail Removed)...
> Hi
> I am makeing a worksheet for the dispatchers ast works.
> This is what I did so far.
> TECH NAME JOB# TIME HHHC STATUS
> LUIS G 1R 8 - 11am fail Completed
> 3R 11 - 2pm
> 5R 2 - 5PM
> Tech name, time, HHHC and status have a drop down list. their reference is
> in cell AA.
>
> So what I need the dispatch to manualy type is the Job#, anything else is
> a
> drop down list or combo.
> I need to have in sheet2 a report of all the jobs that still not completed
> according to their time scheduled.. For example:
>
> TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
> LUIS G 11 - 2PM 1 2
> 2 - 5PM 1
>
>
> My boss wants to know how many jobs still not completed by timeframe and
> the
> technician responsible for that open job. It will be a report that I have
> to
> email several times a day.
>
> I need your help ASAP if possible.
> Thank you
>
>

for some reason is not working. I get a Sintax error.
To give you an idea how my sheet looks, I have a link:
http://cid-515b3cfce2772b8a.skydrive...FILES/TEST.xls
Sheet2 is how the report may look.
Thank you for your help.

"Patrick Molloy" wrote:

> so your table of jobs is Sheet1 and the output will go to sheet2
>
> copy this into a standard code module
>
> Option Explicit
>
> Sub GetReport()
> Dim rowindex As Long
> Dim worker As String
> Dim rw As Long
>
> 'clear target & add headers
> With Worksheets("Sheet2")
> .Cells.Clear
> .Range("A1") = Range("A1")
> .Range("B1") = Range("C1")
> End With
> rowindex = 1
>
> 'check jobs
> For rw = 2 To Range("B1").End(xlDown).Row
> If Cells(rw, 1) <> "" Then
> worker = Cells(rw, 1)
> End If
> 'check status
> If Cells(rw, "E") <> "Completed" Then
> rowindex = rowindex + 1
> With Worksheets("Sheet2")
> .Cells(rowindex, 1) = worker
> .Cells(rowindex, 2) = Cells(rw, 3)
> End With
> End If
> Next
>
> Summarize
>
> End Sub
>
> Sub Summarize()
> With Worksheets("sheet2")
> .Range("E1") = .Range("A1")
> .Range("A1").CurrentRegion.AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=.Range("E1"), _
> Unique:=True
> If .Range("e3") <> "" Then
> .Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
> 1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
> Else
> .Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
> End If
> End With
> End Sub
>
> it checks the Status column, if the word Completed isn't there, then the
> workers name and job time gets copied to the report ( this is sheet2)
> when all the names are read, the summary is added.
>
> To add a code module, go to the development environment, ALT+F11, then from
> the menu choose INSERT then MODULE
>
> this should be enough to get you going. Let me know what else we can help
> with
>
>
>
>
>
> "OscarE" <(E-Mail Removed)> wrote in message
> news58D5F64-EF7B-4565-AF16-(E-Mail Removed)...
> > Hi
> > I am makeing a worksheet for the dispatchers ast works.
> > This is what I did so far.
> > TECH NAME JOB# TIME HHHC STATUS
> > LUIS G 1R 8 - 11am fail Completed
> > 3R 11 - 2pm
> > 5R 2 - 5PM
> > Tech name, time, HHHC and status have a drop down list. their reference is
> > in cell AA.
> >
> > So what I need the dispatch to manualy type is the Job#, anything else is
> > a
> > drop down list or combo.
> > I need to have in sheet2 a report of all the jobs that still not completed
> > according to their time scheduled.. For example:
> >
> > TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
> > LUIS G 11 - 2PM 1 2
> > 2 - 5PM 1
> >
> >
> > My boss wants to know how many jobs still not completed by timeframe and
> > the
> > technician responsible for that open job. It will be a report that I have
> > to
> > email several times a day.
> >
> > I need your help ASAP if possible.
> > Thank you
> >
> >

file is here:
http://cid-b8e56c9a5f311cb7.skydrive...0Files/Martini

run procedure "Main"

full code (in the file)

Option Explicit
Dim rowindex As Long
Sub Main()
GetReport Range("A3:F13"), True
GetReport Range("A18:F29")
GetReport Range("H3:M14")
GetReport Range("H18:M29")
Summarize
End Sub
Sub GetReport(table As Range, Optional first As Boolean = False)
Dim worker As String
Dim rw As Long
Dim cell As Range
'clear target & add headers
If first Then
'initialise
With Worksheets("Sheet2")
.Cells.Clear
.Range("A2:C2") = Array("TECH NAME", "JOBS OPEN", "TIMEFRAME")
End With
rowindex = 2
End If
'check jobs
worker = table.Range("A1")
For Each cell In table.Columns(2).Cells
If cell.Value = "" Then Exit For
If cell.Offset(, 3).Value <> "CP" Then
rowindex = rowindex + 1
With Worksheets("Sheet2")
.Cells(rowindex, 1) = worker
.Cells(rowindex, 2) = cell.Value
.Cells(rowindex, 3) = cell.Offset(, 1)
End With
End If
Next
End Sub
Sub Summarize()
With Worksheets("sheet2")
.Range("E2") = .Range("A2")
.Range("A2").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("E2"), _
Unique:=True
If .Range("e4") <> "" Then
.Range(.Range("E3"), .Range("E3").End(xlDown)).Offset(,
1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
Else
.Range("F3").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
End If
End With
End Sub






"OKY" <(E-Mail Removed)> wrote in message
news:91AF6EF1-525D-44CE-B268-(E-Mail Removed)...
> for some reason is not working. I get a Sintax error.
> To give you an idea how my sheet looks, I have a link:
> http://cid-515b3cfce2772b8a.skydrive...FILES/TEST.xls
> Sheet2 is how the report may look.
> Thank you for your help.
>
> "Patrick Molloy" wrote:
>
>> so your table of jobs is Sheet1 and the output will go to sheet2
>>
>> copy this into a standard code module
>>
>> Option Explicit
>>
>> Sub GetReport()
>> Dim rowindex As Long
>> Dim worker As String
>> Dim rw As Long
>>
>> 'clear target & add headers
>> With Worksheets("Sheet2")
>> .Cells.Clear
>> .Range("A1") = Range("A1")
>> .Range("B1") = Range("C1")
>> End With
>> rowindex = 1
>>
>> 'check jobs
>> For rw = 2 To Range("B1").End(xlDown).Row
>> If Cells(rw, 1) <> "" Then
>> worker = Cells(rw, 1)
>> End If
>> 'check status
>> If Cells(rw, "E") <> "Completed" Then
>> rowindex = rowindex + 1
>> With Worksheets("Sheet2")
>> .Cells(rowindex, 1) = worker
>> .Cells(rowindex, 2) = Cells(rw, 3)
>> End With
>> End If
>> Next
>>
>> Summarize
>>
>> End Sub
>>
>> Sub Summarize()
>> With Worksheets("sheet2")
>> .Range("E1") = .Range("A1")
>> .Range("A1").CurrentRegion.AdvancedFilter _
>> Action:=xlFilterCopy, _
>> CopyToRange:=.Range("E1"), _
>> Unique:=True
>> If .Range("e3") <> "" Then
>> .Range(.Range("E2"), .Range("E2").End(xlDown)).Offset(,
>> 1).FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
>> Else
>> .Range("F2").FormulaR1C1 = "=COUNTIF(C1,RC[-1])"
>> End If
>> End With
>> End Sub
>>
>> it checks the Status column, if the word Completed isn't there, then the
>> workers name and job time gets copied to the report ( this is sheet2)
>> when all the names are read, the summary is added.
>>
>> To add a code module, go to the development environment, ALT+F11, then
>> from
>> the menu choose INSERT then MODULE
>>
>> this should be enough to get you going. Let me know what else we can help
>> with
>>
>>
>>
>>
>>
>> "OscarE" <(E-Mail Removed)> wrote in message
>> news58D5F64-EF7B-4565-AF16-(E-Mail Removed)...
>> > Hi
>> > I am makeing a worksheet for the dispatchers ast works.
>> > This is what I did so far.
>> > TECH NAME JOB# TIME HHHC STATUS
>> > LUIS G 1R 8 - 11am fail Completed
>> > 3R 11 - 2pm
>> > 5R 2 - 5PM
>> > Tech name, time, HHHC and status have a drop down list. their reference
>> > is
>> > in cell AA.
>> >
>> > So what I need the dispatch to manualy type is the Job#, anything else
>> > is
>> > a
>> > drop down list or combo.
>> > I need to have in sheet2 a report of all the jobs that still not
>> > completed
>> > according to their time scheduled.. For example:
>> >
>> > TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
>> > LUIS G 11 - 2PM 1 2
>> > 2 - 5PM 1
>> >
>> >
>> > My boss wants to know how many jobs still not completed by timeframe
>> > and
>> > the
>> > technician responsible for that open job. It will be a report that I
>> > have
>> > to
>> > email several times a day.
>> >
>> > I need your help ASAP if possible.
>> > Thank you
>> >
>> >