I track jobs for techs page one example. Excel 2003

JOB TECH ACCT# STAT ADDRESS
1B 603 162395-7 CP 6844 N DE CHELLY LOOP
3B 617 164655-11 CP 10700 N LA RESERVE DR # 2106
5B 603 267454-1 CP 7270 S SAND DUNE VLY D
4B 634 131976-3 CP 5702 N CAM LAGUNA
3B 609 193005-2 CP 4961 N DIAMOND PL
16B 650 267451-1 XO 9950 N CAM DEL PLATA
1B 636 206822-7 CP 7525 W SUMMER SKY DR
10B 607 120813-8 CP 3028 W WYOMING ST
2B 609 100114-2 RS$ 6430 N MONTROSE DR
3B 603 168731-9 CP 4156 N RIO CANCION # 33
3B 626 231452-8 CP 7990 E SNYDER RD 11 107
I want to take all of ie 603 info in the row and display it on his own page
without having to do it manually. My workbook contains a page for each tech
The page shown above is for the imput and may contain up to 50 unique techs
and 500 jobs there are also additional columns not shown. Each tech may do 10
or more jobs per day and each job is assigned 1b 2b 3b etc. dynamically as
the day progresses. I need to use this format so i can print each techs jobs
out individually at the end of the day. I also move this data via a pivot
table to another sheet that calculates pay for the tech pay and billing for
the company.Billing is address, account number and line item specific. jobs
are referenced by the job number which is dynamic until the end of the day
at which time it is set and can be referenced up to 3 years. I filter these
columns and could filter and copy at the end of the day pasting to their page
but that would be time consuming with 50 techs.

One play to automate it using non-array formulas ..

Sample construct at:
http://www.savefile.com/files/4327849
Auto-Filtering Data To Respective Sheet_mstrouble8_misc

Assume the master list is in sheet: Master
in cols A to E, headers in row1, data from row2 down

Using 50 empty cols to the right of the data, say cols K to BH
List the 50 tech references in K1:BH1, e.g.:
Put in K1: 603
Fill K1 across to BH1 (603, 604, ... 652)

Put in K2: =IF($B2=K$1,ROW(),"")
Copy across to BH2 to cover all the 50 techs
& fill down by say, 600 rows to BH601
to cover the max expected extent of data in the master list

Click Insert Name Define
Put under "Names in workbook:": WSN

Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)

Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.)

In a sheet named: 603
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J$2:$J$600,,MATCH (WSN+0,Master!$K$1:$BH$1,0
)),ROWS($A$1:A1))),"",INDEX(Master!A$2:A$600,MATCH (SMALL(OFFSET(Master!$J$2:
$J$600,,MATCH(WSN+0,Master!$K$1:$BH$1,0)),ROWS($A$ 1:A1)),OFFSET(Master!$J$2:
$J$600,,MATCH(WSN+0,Master!$K$1:$BH$1,0)),0)))

Copy A2 across to E2, fill down to say, E20
(copy down just enough to cover the max expected
no. of jobs per tech)

Cols A to E will return only the lines for tech: 603 from "Master",
all lines neatly bunched at the top

Now, just make a copy of the sheet: 603, rename it as: 604
and we'd get the results for tech: 604.

Repeat the copy rename sheet process
to get the rest of the 50 tech sheets (a one-time job)

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"mstrouble81" wrote in message
...
I track jobs for techs page one example. Excel 2003

JOB TECH ACCT# STAT ADDRESS
1B 603 162395-7 CP 6844 N DE CHELLY LOOP
3B 617 164655-11 CP 10700 N LA RESERVE DR # 2106
5B 603 267454-1 CP 7270 S SAND DUNE VLY D
4B 634 131976-3 CP 5702 N CAM LAGUNA
3B 609 193005-2 CP 4961 N DIAMOND PL
16B 650 267451-1 XO 9950 N CAM DEL PLATA
1B 636 206822-7 CP 7525 W SUMMER SKY DR
10B 607 120813-8 CP 3028 W WYOMING ST
2B 609 100114-2 RS$ 6430 N MONTROSE DR
3B 603 168731-9 CP 4156 N RIO CANCION # 33
3B 626 231452-8 CP 7990 E SNYDER RD 11 107
I want to take all of ie 603 info in the row and display it on his own
page
without having to do it manually. My workbook contains a page for each
tech
The page shown above is for the imput and may contain up to 50 unique
techs
and 500 jobs there are also additional columns not shown. Each tech may do
10
or more jobs per day and each job is assigned 1b 2b 3b etc. dynamically as
the day progresses. I need to use this format so i can print each techs
jobs
out individually at the end of the day. I also move this data via a pivot
table to another sheet that calculates pay for the tech pay and billing
for
the company.Billing is address, account number and line item specific.
jobs
are referenced by the job number which is dynamic until the end of the
day
at which time it is set and can be referenced up to 3 years. I filter
these
columns and could filter and copy at the end of the day pasting to their
page
but that would be time consuming with 50 techs.

30,000 helper cells!

Biff

"Max" wrote in message
...
One play to automate it using non-array formulas ..

Sample construct at:
http://www.savefile.com/files/4327849
Auto-Filtering Data To Respective Sheet_mstrouble8_misc

Assume the master list is in sheet: Master
in cols A to E, headers in row1, data from row2 down

Using 50 empty cols to the right of the data, say cols K to BH
List the 50 tech references in K1:BH1, e.g.:
Put in K1: 603
Fill K1 across to BH1 (603, 604, ... 652)

Put in K2: =IF($B2=K$1,ROW(),"")
Copy across to BH2 to cover all the 50 techs
& fill down by say, 600 rows to BH601
to cover the max expected extent of data in the master list

Click Insert Name Define
Put under "Names in workbook:": WSN

Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)

Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came
from
a post by Harlan.)

In a sheet named: 603
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J$2:$J$600,,MATCH (WSN+0,Master!$K$1:$BH$1,0
)),ROWS($A$1:A1))),"",INDEX(Master!A$2:A$600,MATCH (SMALL(OFFSET(Master!$J$2:
$J$600,,MATCH(WSN+0,Master!$K$1:$BH$1,0)),ROWS($A$ 1:A1)),OFFSET(Master!$J$2:
$J$600,,MATCH(WSN+0,Master!$K$1:$BH$1,0)),0)))

Copy A2 across to E2, fill down to say, E20
(copy down just enough to cover the max expected
no. of jobs per tech)

Cols A to E will return only the lines for tech: 603 from "Master",
all lines neatly bunched at the top

Now, just make a copy of the sheet: 603, rename it as: 604
and we'd get the results for tech: 604.

Repeat the copy rename sheet process
to get the rest of the 50 tech sheets (a one-time job)

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"mstrouble81" wrote in message
...
I track jobs for techs page one example. Excel 2003

JOB TECH ACCT# STAT ADDRESS
1B 603 162395-7 CP 6844 N DE CHELLY LOOP
3B 617 164655-11 CP 10700 N LA RESERVE DR # 2106
5B 603 267454-1 CP 7270 S SAND DUNE VLY D
4B 634 131976-3 CP 5702 N CAM LAGUNA
3B 609 193005-2 CP 4961 N DIAMOND PL
16B 650 267451-1 XO 9950 N CAM DEL PLATA
1B 636 206822-7 CP 7525 W SUMMER SKY DR
10B 607 120813-8 CP 3028 W WYOMING ST
2B 609 100114-2 RS$ 6430 N MONTROSE DR
3B 603 168731-9 CP 4156 N RIO CANCION # 33
3B 626 231452-8 CP 7990 E SNYDER RD 11 107
I want to take all of ie 603 info in the row and display it on his own
page
without having to do it manually. My workbook contains a page for each
tech
The page shown above is for the imput and may contain up to 50 unique
techs
and 500 jobs there are also additional columns not shown. Each tech may
do
10
or more jobs per day and each job is assigned 1b 2b 3b etc. dynamically
as
the day progresses. I need to use this format so i can print each techs
jobs
out individually at the end of the day. I also move this data via a pivot
table to another sheet that calculates pay for the tech pay and billing
for
the company.Billing is address, account number and line item specific.
jobs
are referenced by the job number which is dynamic until the end of the
day
at which time it is set and can be referenced up to 3 years. I filter
these
columns and could filter and copy at the end of the day pasting to their
page
but that would be time consuming with 50 techs.

"Biff" wrote:
30,000 helper cells!

Ah, but that doesn't mean it doesn't/won't work <g
It's just a suggestion for the OP to try out, of course ..
The number may be sizeable, but the formulas are simple
The longer extract formulas on each tech's sheet
are kept to a sufficiently modest ~100 cells per sheet (5K total)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

An alternative without helpers <g

http://cjoint.com/?mtladwumrn

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Max" wrote in message
...
"Biff" wrote:
30,000 helper cells!

Ah, but that doesn't mean it doesn't/won't work <g
It's just a suggestion for the OP to try out, of course ..
The number may be sizeable, but the formulas are simple
The longer extract formulas on each tech's sheet
are kept to a sufficiently modest ~100 cells per sheet (5K total)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

And another alternative without the use of any formulae, using Pivot Tables

http://cjoint.com/?mtmBxA15YR

Regards

Roger Govier


Bob Phillips wrote:
An alternative without helpers <g

http://cjoint.com/?mtladwumrn