Status dependent on status of parent or child row

Krystin

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!

Brian_Richardson

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

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:

Krystin

Thank you so much Brian! This did the trick!