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
-
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.
- Is Status set on the row? If so, make Calculated Status = Status
- 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
- 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:
Answers
-
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.
- Is Status set on the row? If so, make Calculated Status = Status
- 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
- 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:
-
Thank you so much Brian! This did the trick!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!