Status dependent on status of parent or child row

Hello,

I am trying to help another team solution a request related to managing projects. Their requirements are below:

There are four levels in the object hierarchy 1. Initiatives 2. Projects 3. Tasks 4. Sub-tasks

There are six different statuses – To Do, In Progress, Blocked, On Hold, Completed and Canceled.

If an object is changed to In Progress, everything up-level is also set to In Progress, but it does not affect anything down-level (e.g., if a task is set to In Progress, its Project and Initiative are set to In Progress, but its Sub-tasks are unaffected)

If an object is set to To Do, Blocked, On Hold, Completed or Canceled, everything down-level is set to the same status, but nothing up-level is affected

Based on my level of knowledge, I believe there will need to be a column where you enter the status and another column that displays the logic above. I don't currently have a meaningful screenshot as I have just been playing around in a test sheet. Any help would be greatly appreciated!

Thank you!

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/14/24 Answer ✓

    Yes you're on the right track, you cannot enter data into the same cell as a column formula, and you're going to want a column formula for this since my guess is that the various tasks at various levels will change…and you don't want people to have to copy/paste formulas every time they add a task.

    We do something similar here with status, and have two columns… one is the actual reported status that is calculated, and the other is a status override column that people can use to set status to override the calculated or default status.

    I'm going to assume your "entered" status column is cleverly called Status :-)

    Setup a column called Calculated Status.

    The formula below runs in the following order on each row.

    1. Is Status set on the row? If so, make Calculated Status = Status
    2. If not, then do any Ancestor tasks up the chain have To Do, Blocked, etc set? If so then set Calculated Status = Ancestor's status
    3. If no ancestors have status set, then do any Descendants have "In Progress" set? If so then set Calculated Status = "In Progress".

    Here's the formula. After copying this in, right click it and choose Convert to Column Formula to apply it to all rows regardless of future changes.

    =IF(Status@row <> "", Status@row, IF(COUNTIF(ANCESTORS(Status@row), "To Do") > 0, "To Do", IF(COUNTIF(ANCESTORS(Status@row), "Blocked") > 0, "Blocked", IF(COUNTIF(ANCESTORS(Status@row), "On Hold") > 0, "On Hold", IF(COUNTIF(ANCESTORS(Status@row), "Completed") > 0, "Completed", IF(COUNTIF(ANCESTORS(Status@row), "Canceled") > 0, "Canceled", IF(COUNTIF(DESCENDANTS(Status@row), "In Progress") > 0, "In Progress")))))))

    Here's an example of how this plays out on a sheet:

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/14/24 Answer ✓

    Yes you're on the right track, you cannot enter data into the same cell as a column formula, and you're going to want a column formula for this since my guess is that the various tasks at various levels will change…and you don't want people to have to copy/paste formulas every time they add a task.

    We do something similar here with status, and have two columns… one is the actual reported status that is calculated, and the other is a status override column that people can use to set status to override the calculated or default status.

    I'm going to assume your "entered" status column is cleverly called Status :-)

    Setup a column called Calculated Status.

    The formula below runs in the following order on each row.

    1. Is Status set on the row? If so, make Calculated Status = Status
    2. If not, then do any Ancestor tasks up the chain have To Do, Blocked, etc set? If so then set Calculated Status = Ancestor's status
    3. If no ancestors have status set, then do any Descendants have "In Progress" set? If so then set Calculated Status = "In Progress".

    Here's the formula. After copying this in, right click it and choose Convert to Column Formula to apply it to all rows regardless of future changes.

    =IF(Status@row <> "", Status@row, IF(COUNTIF(ANCESTORS(Status@row), "To Do") > 0, "To Do", IF(COUNTIF(ANCESTORS(Status@row), "Blocked") > 0, "Blocked", IF(COUNTIF(ANCESTORS(Status@row), "On Hold") > 0, "On Hold", IF(COUNTIF(ANCESTORS(Status@row), "Completed") > 0, "Completed", IF(COUNTIF(ANCESTORS(Status@row), "Canceled") > 0, "Canceled", IF(COUNTIF(DESCENDANTS(Status@row), "In Progress") > 0, "In Progress")))))))

    Here's an example of how this plays out on a sheet:

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Krystin
    Krystin ✭✭✭

    Thank you so much Brian! This did the trick!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!