How to combine multiple project plans into one, but keep separate editing access?

cohlrich

I am running a program that contains 17 different workstreams, and each workstream has a separate PM that is running their own individual plan. We want a view where you can see all of the workstreams together in a 'program' plan. We consolidated all plans into one but this has created a large number of issues as we have 20 people all trying to edit and update at the same time. I want to allow for the PM's to have their own plan in their own sheet, and pull all of this into one sheet that is just viewing for everybody. I have tried to create a report with all the sheets but this removes the hierarchy of their activities. I also want to tag dependencies to separate workstreams. So can you tag a predecessor in another sheet that connects all of these together? Please let me know any suggestions to get this to work because having 20 people in the same sheet editing at the same time does not work!

SoS | Dan Palenchar

You can either have one large Sheet of all work streams and separate with reports for individual PMs.

You could also do what you have and aggregate multiple Sheets together to a report. To mantain hierarchy, you could add a column in each of your Sheets with a formula to identify whatever hierarchy is meaningful to you using =COUNT(CHILDREN()) or =COUNT(ANCESTORS()) and then group your report by that column. You could fancy and use an IF() statement to output text based on hierarchy. For example, if you have:

-Hierarchy 1 (parent) = Project

-Hierarchy 2 = Task

-Hierarchy 3 and higher = Sub-task

You could do something like (might be a type in formula, did it off the top of my head)

=IF(COUNT(ANCESTORS() = 0, "Project", IF(COUNT(ANCESTORS() = 1, "Task", "Sub-Task"))

This would then be usable to group your report by this column and you could have Project at the top followed by task then sub task, etc.