Project Schedule - automate Header Status

Anne-Solene Monrouzeau

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

1. 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.
2. If tasks are either complete-on time / complete-late – Not Started, then header is in progress.
3. If all tasks are complete – on time, then header status is complete.
4. If all tasks are complete – late, then header status is complete.
5. 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

David Joyeuse

Hi @Anne-Solene Monrouzeau

If I'm right I can sum up what you want in this order:

1. If 1 children status is "Blocked", then parent is "Blocked".
2. If 1 children status is "Late", then parent is "Late".
3. If 1 children status is "In Progress" or "Not Started", then parent is "In Progress".
4. 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:

1. =IF(COUNTIFS(CHILDREN(), "Blocked")>0, "Blocked")
2. =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!

David Joyeuse

Anne-Solene Monrouzeau

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!