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

Boring! <vbg

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

Could this work in my problem? I posted over here a VBA code that
needs to be modifed (see he
http://groups.google.com/group/micro...4418ccfcc4a286


But if I could use an array formula like y'all are using here, that
would work just as well for me. Background: I have two sheets of
stock data from a stock screener, both same
parameters, just different time frames (i.e., one sheet is newer
whenever I import new a new screen from the Internet), which will pull
different stocks as time passes. I'm trying to compare the two sheets
and extract the stock data (a whole row's worth of data, not just a
cell) that is unique to the "Last Import" sheet. This would allow any
new stocks identified on the screener to be transferred to a new sheet
(called "Filtered List"). That way I don't have to research the same
stocks over and over, only the new ones that show up with each import.

I can email the file if you need to see what I'm talking about...thanks
in advance for the help!

Great alternatives, Bob & Roger !
Think the OP would be spoilt for choice with the variety here <bg
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Pivot tables <argh!

Biff

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

GREAT JOB GUYS I APPRECIATE ALL THE HELP!!!!!!!!! I TRIED THEM ALL AND WENT
WITH BOB, BECAUSE HIS WAS EASIEST FOR ME TO UNDERSTAND AND IMPLEMENT AT MY
SKILL LEVEL. THIS WAS MY FIRST POST TO THIS SITE AND I WAS AMAZED AT THE
RESPONSE. AGAIN I APPRECIATE THE HELP

"Biff" wrote:

Pivot tables <argh!

Biff

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

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I have a couple of more things I would like to do with this spread sheet
maybe you can help.

I'm not sure the best way to do this but on the master page I would like to
figure out a way to attach a picture file to each job number. The picture
file is only about 20kb but the problem is that i'm sharing this workbook
between all my network computers and I am unable to attach a picture while
shared. What I was pondering was is there a way to put a placeholder of some
sort where when the pics come in from the camera phones from the job site can
i direct them to a placeholder based on the tech and job number? Should I
bring them into another sheet and import them all at once at the end of the
day so my spreadsheet isn't so big? One of the problems I'm having now that
I've implemented the new design is that it keeps recalculating and the
current size for an average day is 7MB I can shut off recalculation but it
takes me from viewing real time as someone always forgets to manually
recalculate. Any Suggestions?

I also wanted to pull the account and address information from each tech to
another spreadsheet where you get the following.

tech number

account
address
account
address
account
address
and so on for each tech. I realize I should be able to figure this out
based on the last formulas you wrote for me. but i've tried to make it work
and i'm not sure i am going to be able to figure it out.


"Max" wrote:

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sorry, I'm afraid I'm out of ideas here to offer you. Hang around awhile
for possible insights from others to flow in here. Alternatively, you may
wish to put in your new queries as new posts. And it's always best to keep
it to one query per post.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--