Project Schedule - automate Header Status
Hi,
We are new to Smartsheet. We are migrating our project implementation schedule to Smartsheet. One of the gap we have is that Smartsheet does not calculate the header status automatically. See cell in yellow below. The current formula does not handle the status complete properly and has other issues. Does someone have any idea how to build an or statement or other suggestion on how we could build that formula?
What we want:
Statuses: Not Started, In Progress, Late, Blocked, Complete – on time / Complete – Late
- If 1 task status is in progress / Late / Blocked, then header status to be changed to that status. Blocked status takes precedence over late and in progress. Late status takes precedence over In progress.
- If tasks are either complete-on time / complete-late – Not Started, then header is in progress.
- If all tasks are complete – on time, then header status is complete.
- If all tasks are complete – late, then header status is complete.
- If tasks are a combination of complete – on time and complete – late, then status is complete.
Current formula
=IFERROR(IF(Hierarchy@row = 1, IF(COUNTIF(CHILDREN(), "Late") / COUNT(CHILDREN()) >= 0.1, "Late",
IF(COUNTIF(CHILDREN(), "Blocked") / COUNT(CHILDREN()) >= 0.1, "Blocked",
IF(COUNTIF(CHILDREN(), "In Progress") / COUNT(CHILDREN()) >= 0.1, "In Progress",
IF(COUNTIF(CHILDREN(), "Not Started") / COUNT(CHILDREN()) >= 0.1, "Not Started",
IF(COUNTIF(CHILDREN(), "Complete - Late") / COUNT(CHILDREN()) = 1, "Complete - Late",
IF(COUNTIF(CHILDREN(), "Complete - On Time") / COUNT(CHILDREN()) = 1, "Complete - On Time", "")))))), ""), "")
Thanks!
Anne-Solene
Answers
-
If I'm right I can sum up what you want in this order:
- If 1 children status is "Blocked", then parent is "Blocked".
- If 1 children status is "Late", then parent is "Late".
- If 1 children status is "In Progress" or "Not Started", then parent is "In Progress".
- If all children status are "Complete - Late", "Complete - on time" or a mix of that, then parent is "Complete".
Formulas for each of 1 & 2:
- =IF(COUNTIFS(CHILDREN(), "Blocked")>0, "Blocked")
- =IF(COUNTIFS(CHILDREN(), "Late")>0, "Late")
Now we'll do 3 & 4 in one formula. If we reach this nested IF statement, it already means that No Children is "Blocked" or "Late". Leaving options to being "In Progress", "Not Started", "Complete - on time" or "Complete - Late".
So we'll go like this:
= IF(COUNTIFS(CHILDREN(), FIND("Complete", @cell)>0)=COUNT(CHILDREN()), "Complete", "In Progress")
Here we're just looking for the string "Complete" that is common to both Complete state and it will cover all your options (all late, all on time or a mix of it) in one formula. If the count isn't equal the count of children,then it means at least one is either "In Progress" or "Not Started".
To sum this up:
=IF(COUNTIFS(CHILDREN(), "Blocked")>0, "Blocked", IF(COUNTIFS(CHILDREN(), "Late")>0, "Late", IF(COUNTIFS(CHILDREN(), FIND("Complete", @cell)>0)=COUNT(CHILDREN()), "Complete", "In Progress")))
Hope it helped!
-
Hi - sorry I did not see the response. I think I followed the same approach to fix the formula and it worked.
Thanks for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!