Job List in one workbook broken down to Account reps in anotherworkbook.

[M G Henry]

I am wanting to find a way to update between two workbooks, one is a
job number workbook that is constantly updated by 15 different
people, the other is a job detail workbook that I want to track by
account rep.

There are 2 fields that are entered. The job number is an auto
number field which is not manually entered. The two that are are the
job description and the account rep.

This list is very long, probably exceeding 4000 jobs per year.

In another workbook I am tracking job detalis pertaining to the job
number and tracking them by account reps.

What I am looking to do in the second workbook is have tabs for each
of our fifteen account reps with only the jobs that they have entered
on their own tabs.

This way I can have the account reps populate the job number field and
description field automatically from what was entered in the job
number workbook under their name.

The issue is that the job number workbook is updated constantly and I
would only want the jobs that were entered since the last time the
detail workbook was opened to be appended to the lists for each
account rep.

How would I go about this update process between workbooks. ?

Thank you in advance for your timely assistance.

 

[Leo Rod]

ok, you have a list with:
2 fields: job description and Account rep.
1 autonumber field -non updatable once generated- to id the job

And another workbook tracking advance on the jobs using the field that the
reps. are filling.
On this "second" workbook you want to have individual sheets with the sales
rep name and the update of the job linking what they have done (from the 1st
workbook) with your info on this second workbook.

My advise for a multi sheet solution:

From your second workbook, take all the data -already organized as list- and
append a "vlookup" function to create new columns retrieving the information
that you do not have from the sales rep. spreadsheet -where they updated and
entered the jobs- and with all the data consolidated in one sheet populate a
pivot table including all the this data organized by sales respresentative
as the only "page field" - later you can include more "page fields" to
filter further.

This will address half of your question.

Regarding the updatability "based on a date", if you're not having the sales
rep identify when was the job entered or updated create a field for that in
their spreadsheet or in yours -based on your own criteria of usefullyness-
if it's on me I will create it on theirs so you'll be able to control when
was the "job" created and when was the spreadsheet opened for the last
time -this you have to do it by hand- and have this field evaulate today's
date to create a discriminator to display the data on the pivot table,
something like this:

=If(vlookup("job_#","workbook#1_lookup_area","number of the column from left
to right where the date is",false)>(Today()-7),"Show","Hide")

This formula calls for a validator output that can be read and integrated
into the pivot table page field to filter and show only items that are at
least one week old.
If the formula "Today()-7" is replaced by a cell value "A5" where "A5" (or
any cell that you desire) is 10/14/2008 it will generate: "Show" only on
those newer than 10/14/2008 and "hide" for all others. with this on the
pivot table you can filter and change it as you wish, and every time you
update the pivot tables it will show what you want.

Once the table is ready generate "pages" of this pivot table by "sales rep",
filtering only records with the qualificative "Show" this will create one
updatable page by sales agent.
Separate this pages from your workbook (second workbook) and hand them
copies. If you're working on a network where they can "see" your workbook
the pivot tables will update when they refresh the data. if you're working
out of a network you'll need to send periodical updates to these people
having the data changed to the last time that they modified their
spreadhseet, this is you entering the date by hand and populating the pivot
table report

 

[M G Henry]

ok, you have a list with:
2 fields: job description and Account rep.
1 autonumber field -non updatable once generated- to id the job

And another workbook tracking advance on the jobs using the field that the
reps. are filling.
On this "second" workbook you want to have individual sheets with the sales
rep name and the update of the job linking what they have done (from the 1st
workbook) with your info on this second workbook.

My advise for a multi sheet solution:

From your second workbook, take all the data -already organized as list- and
append a "vlookup" function to create new columns retrieving the information
that you do not have from the sales rep. spreadsheet -where they updated and
entered the jobs- and with all the data consolidated in one sheet populate a
pivot table including all the this data organized by sales respresentative
as the only "page field" - later you can include more "page fields" to
filter further.

This will address half of your question.

Regarding the updatability "based on a date", if you're not having the sales
rep identify when was the job entered or updated create a field for that in
their spreadsheet or in yours -based on your own criteria of usefullyness-
if it's on me I will create it on theirs so you'll be able to control when
was the "job" created and when was the spreadsheet opened for the last
time -this you have to do it by hand-  and have this field evaulate today's
date to create a discriminator to display the data on the pivot table,
something like this:

=If(vlookup("job_#","workbook#1_lookup_area","number of the column fromleft
to right where the date is",false)>(Today()-7),"Show","Hide")

This formula calls for a validator output that can be read and integrated
into the pivot table page field to filter and show only items that are at
least one week old.
If the formula "Today()-7" is replaced by a cell value "A5" where "A5" (or
any cell that you desire) is 10/14/2008 it will generate: "Show" only on
those newer than 10/14/2008 and "hide" for all others. with this on the
pivot table you can filter and change it as you wish, and every time you
update the pivot tables it will show what you want.

Once the table is ready generate "pages" of this pivot table by "sales rep",
filtering only records with the qualificative "Show" this will create one
updatable page by sales agent.
Separate this pages from your workbook (second workbook) and hand them
copies. If you're working on a network where they can "see" your workbook
the pivot tables will update when they refresh the data. if you're working
out of a network you'll need to send periodical updates to these people
having the data changed to the last time that they modified their
spreadhseet, this is you entering the date by hand and populating the pivot
table report













- Show quoted text -

Thanks I will give that a try.