Auto-populate project phase in another sheet

Shonda

Hope someone can help me with this question....

I would like to auto-populate a project phase (i.e. initiate, plan, execute, monitor&control, close) in another sheet based on completion of phase in project schedule. In essence, when 100% of the tasks are completed for a phase (let's say plan) in the schedule, i would like to link the next phase (execute) to another sheet - is this possible?

Paul Newcome

I would start by inserting a hidden text/number column (we can call it "Level" in this example) with this column formula:

=COUNT(ANCESTORS([Task Name]@row))

Then in your other sheet you would use something like this...

=INDEX(COLLECT({Project Plan Task Name Column}, {Project Plan Level Column}, @cell = 1, {Project Plan % Column}, @cell <> 1), 1)

Paul Newcome

Are you able to provide some screenshots for reference? Generally speaking it is possible, but without seeing your structure it is hard to provide an actual solution.

Shonda

Hi Paul.. I snagged an image of project schedule...... once all the tasks are 100% complete for initiate phase, is there a formula that i could use or a way i could link to another sheet that we are in the plan phase? The sheet I want to link out too is an intake sheet that has a project phase column. We are doing things manually, so i have to go in and update the phase manually on the intake sheet. I was thinking about creating a master metric sheet for all projects and just link the phase from the metric sheet to the intake sheet.

Paul Newcome

I would start by inserting a hidden text/number column (we can call it "Level" in this example) with this column formula:

=COUNT(ANCESTORS([Task Name]@row))

Then in your other sheet you would use something like this...

=INDEX(COLLECT({Project Plan Task Name Column}, {Project Plan Level Column}, @cell = 1, {Project Plan % Column}, @cell <> 1), 1)

Shonda

Paul, that worked!!!! Thank you very much

Paul Newcome

Happy to help. 👍️