OK, you guys have helped me out of some tight spots in the past...hope the
trend continues! here's my dilemma:

I have a file that contains 26 sheets. Sheet 1 is a Chart compiled of info
from Sheets 2-18. There are listings of potential Clients on these sheets &
the amount of work that my Project Mgrs have done (therefore, each sheet tab
is labeled with the PM's name). Lots of VLOOKUP formulas hidden in there!
There's a column that also contains the name of the Partner in charge of each
Client. This is important..

Sheet 19 is a Chart compiled of info from Sheets 20-26. The info on Sheets
20-26 is actually the Partner info that I copy/pasted from sheets 2-18 BUT,
now my boss wants the sheets to automatically update whenever the PMs change
the info on sheets 2-18, namely so I don't have to spend so much time doing
the copy/paste thing.

So, long story, short version: I need a formula that will extract the info
for each Partner each time the PMs update the file. If there are 5 sheets
(let's say they've been renamed to: LONG, BROWN, SHORT, HOWARD, GATES) and I
want to pull info on each of those sheets where my Partner (let's call him
BOSS1) is listed as the Partner & have it automatically update on my boss's
sheet, what's the lookup? I've taken a stab at this already, much to the
dismay of my now-aching head! So I'm leaving it in your hands :)

Thanks in advance!! (Hope I haven't confused you too much..)

Hi
I'd use a database application for this :-)

For a more specific answer it would be good if you provide more
details:
- what values to update
- where are they stored
- what is the exact output

But as said: Don't think Excel is the right tool for this.

--
Regards
Frank Kabel
Frankfurt, Germany

"Liz-In-USA" schrieb im
Newsbeitrag ...
OK, you guys have helped me out of some tight spots in the
past...hope the
trend continues! here's my dilemma:

I have a file that contains 26 sheets. Sheet 1 is a Chart compiled
of info
from Sheets 2-18. There are listings of potential Clients on these
sheets &
the amount of work that my Project Mgrs have done (therefore, each
sheet tab
is labeled with the PM's name). Lots of VLOOKUP formulas hidden in
there!
There's a column that also contains the name of the Partner in charge
of each
Client. This is important..

Sheet 19 is a Chart compiled of info from Sheets 20-26. The info on
Sheets
20-26 is actually the Partner info that I copy/pasted from sheets
2-18 BUT,
now my boss wants the sheets to automatically update whenever the PMs
change
the info on sheets 2-18, namely so I don't have to spend so much time
doing
the copy/paste thing.

So, long story, short version: I need a formula that will extract the
info
for each Partner each time the PMs update the file. If there are 5
sheets
(let's say they've been renamed to: LONG, BROWN, SHORT, HOWARD,
GATES) and I
want to pull info on each of those sheets where my Partner (let's
call him
BOSS1) is listed as the Partner & have it automatically update on my
boss's
sheet, what's the lookup? I've taken a stab at this already, much to
the
dismay of my now-aching head! So I'm leaving it in your hands :)

Thanks in advance!! (Hope I haven't confused you too much..)

ok, I'll try to provide more detail first (wondering if I've been on the
wrong page all along & perhaps a Pivot Table might not be the way to go):

1. Sheet Name = LONG
Company Name Partner Goal1 Goal2 Goal3 Total
ABC, Inc. Boss 1 5 10 15 30
XYZ Corp. Boss 1 10 20 30 60
Redline Inc. Boss 3 5 5 5 15
Blueline Corp. Boss 7 10 0 15 25

2. Sheet Name = GATES
Company Name Partner Goal1 Goal2 Goal3 Total
Tax R Us. Boss 1 5 10 15 30
Toyland Corp Boss 4 10 20 30 60
Metro Shuttle Boss 6 5 5 5 15
PuterIssues, Inc Boss 1 10 0 15 25

OK, just as an example (again, I have 26 sheets with the Project Manager
names), I want to take all instances of Boss 1 (with the info for Co name,
Partner, & the Goal figures -- my actual file goes all the way thru Column Y,
but every other column from C - Y has VLOOKUP info hidden) Periodically the
project Mgrs (Long & Gates) have to update the Goals info (The Totals column
has a formula which totals up all the Goal info)

3. Sheet Name = BOSS 1
Company Name Partner Goal1 Goal2 Goal3 Total
ABC, Inc. Boss 1 5 10 15 30
XYZ Corp. Boss 1 10 20 30 60
Tax R Us. Boss 1 5 10 15 30
PuterIssues, Inc Boss 1 10 0 15 25

I renamed a sheet BOSS 1, then went back thru all the Project Mgr sheets &
copied only Boss 1's info & pasted it into Sheet 3. Tedious, yes...but it
works. Only thing is that currently, as the Project Mgrs update their sheet,
BOSS1's sheet does not automatically get updated. (this also has to be
completed for all 7 bosses) -- Unfortunately, putting this info into a
database isn't really an option either; everyone's used to doing it this
way......If it's too much trouble, I may just have to stick with the
copy/paste method. Meanwhile, I'll keep trying some other options
too..thanks again!

"Frank Kabel" wrote:

Hi
I'd use a database application for this :-)

For a more specific answer it would be good if you provide more
details:
- what values to update
- where are they stored
- what is the exact output

But as said: Don't think Excel is the right tool for this.

--
Regards
Frank Kabel
Frankfurt, Germany

"Liz-In-USA" schrieb im
Newsbeitrag ...
OK, you guys have helped me out of some tight spots in the
past...hope the
trend continues! here's my dilemma:

I have a file that contains 26 sheets. Sheet 1 is a Chart compiled
of info
from Sheets 2-18. There are listings of potential Clients on these
sheets &
the amount of work that my Project Mgrs have done (therefore, each
sheet tab
is labeled with the PM's name). Lots of VLOOKUP formulas hidden in
there!
There's a column that also contains the name of the Partner in charge
of each
Client. This is important..

Sheet 19 is a Chart compiled of info from Sheets 20-26. The info on
Sheets
20-26 is actually the Partner info that I copy/pasted from sheets
2-18 BUT,
now my boss wants the sheets to automatically update whenever the PMs
change
the info on sheets 2-18, namely so I don't have to spend so much time
doing
the copy/paste thing.

So, long story, short version: I need a formula that will extract the
info
for each Partner each time the PMs update the file. If there are 5
sheets
(let's say they've been renamed to: LONG, BROWN, SHORT, HOWARD,
GATES) and I
want to pull info on each of those sheets where my Partner (let's
call him
BOSS1) is listed as the Partner & have it automatically update on my
boss's
sheet, what's the lookup? I've taken a stab at this already, much to
the
dismay of my now-aching head! So I'm leaving it in your hands :)

Thanks in advance!! (Hope I haven't confused you too much..)

Instead of all maintaining the Project Manager and Partner sheets, you
could use a macro to update them as required.

Store all the data on the master sheet. You can create a pivot table to
summarize the data, or filter to see data for a specific project manager
or partner.

Keep a backup copy of the file, and let the users update their data on
the master sheet.

If you need to create individual sheets, you can use a macro similar to
the one he

http://www.contextures.com/excelfiles.html


Under the heading 'Filter', look for 'Update Sheets from Master'


Liz-In-USA wrote:
OK, you guys have helped me out of some tight spots in the past...hope the
trend continues! here's my dilemma:

I have a file that contains 26 sheets. Sheet 1 is a Chart compiled of info
from Sheets 2-18. There are listings of potential Clients on these sheets &
the amount of work that my Project Mgrs have done (therefore, each sheet tab
is labeled with the PM's name). Lots of VLOOKUP formulas hidden in there!
There's a column that also contains the name of the Partner in charge of each
Client. This is important..

Sheet 19 is a Chart compiled of info from Sheets 20-26. The info on Sheets
20-26 is actually the Partner info that I copy/pasted from sheets 2-18 BUT,
now my boss wants the sheets to automatically update whenever the PMs change
the info on sheets 2-18, namely so I don't have to spend so much time doing
the copy/paste thing.

So, long story, short version: I need a formula that will extract the info
for each Partner each time the PMs update the file. If there are 5 sheets
(let's say they've been renamed to: LONG, BROWN, SHORT, HOWARD, GATES) and I
want to pull info on each of those sheets where my Partner (let's call him
BOSS1) is listed as the Partner & have it automatically update on my boss's
sheet, what's the lookup? I've taken a stab at this already, much to the
dismay of my now-aching head! So I'm leaving it in your hands :)

Thanks in advance!! (Hope I haven't confused you too much..)



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html