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

  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

Answers

  • David Joyeuse
    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!

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!