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