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 completeon time / completelate – 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!
AnneSolene
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
Check out the Formula Handbook template!