Smartsheet PMO template - Status

Question

Hello,

New to the forum, appreciate feedback from anyone experienced.

I've downloaded and using the Smartsheet PMO template.

A question regarding the Status column in the Project Plan Sheet, I don't seem to see any high-level formulas to calculate the status in the first row (Parent) for overall summary.

Is this manually selected by the Project Manager and if so, would it be kind of irrelevant that once a project has initiated "some level" of activity, the majority of time would be in Status of "In Progress" in the Parent row, even though many of the activities in the children sub-tasks could be Not Started, In Progress, On Hold or Cancelled?

Thanks for any advice.

Adrien Leduc · Answer

Sorry I double posted, I can't see an option to delete this message.

Adrien Leduc · Answer

I agree with Aravind, I would do this with nested IF. I've done one in the past, not based on % but on children status. You have to build a logic: if all children = complete, then complete, if one has in progress, then in progress, if all cancelled, then cancelled, if all not started, not started, etc…

If you post your sheet with all your status and your logic someone can probably help write the formula if you struggle, or you can try to describe it to the AI, or you can try yourself with trial and errors, that's a good opportunity to learn.

AravindGP · Answer

Hi @emlhk

You can write a formula to get the parent row calculated. This could be based on the % Complete column or based on the child rows. If it is based on the % Complete column, the formula could be =IF([% Complete]@row=1, "Complete", IF([% Complete]@row>0, "In Progress", "Not Started"))

If it is to be based on child rows, you will need to define what number of Not Started, In Progress, On Hold, Complete, and Cancelled would constitute the parent row's status. Happy to help with the formula for this if you can share your definitions.