Auto-populate project phase in another sheet

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?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

Answers

  • Paul Newcome
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Shonda ✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!