RYG health and status indicator roll-up


I currently have something similar like this setup for a number of projects, for each of the deliverables, I use the following formula:

=IF(Status4 = "Complete", "Blue", IF(Status4 = "At Risk", "Yellow", IF(Status4 = "On Hold", "Yellow", IF(ISBLANK(Finish4), "", IF(Finish4 - TODAY() < 0, "Red", IF(Finish4 - TODAY() < 3, "Yellow", "Green"))))))


For Project and initiative level, I use the following:

=IF(OR(COUNTIF(CHILDREN(), "Red") > 0, COUNTIF(CHILDREN(), "Yellow") > 0), "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", ""))


There are a number of things that are not working with the current setup that I would need help with:

  • Project and Initiative level does not react to a red health indicator from one of the children (not working)
  • Finish in the past should always overwrite yellow status (not working)


Any other good suggestions are greatly welcome. Thank you!

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @userxyz

    According to what you want to do, your second formula isn't properly set up.

    For project and Initiative:

    If you want green to overwrite yellow status, then it must be your first statement in your IF function.

    Second, your OR function is around Red and Yellow. If either one appears, then the function will return Yellow.

    Deliverables:

    Here again watch out as you're meddling 2 differents columns that may get the same result.

    If the Status is either "At Risk" or "On Hold", the health will be yellow, no matter what the Finish Date is, because those statements are prior to the Finish ones. If you want Finish to overwrite everything, it has to be first in your formula.

    Smartsheet checks formulas the way they are written. If one statement is true within an IF function, it'll stop here and won't check the following ones.

    Hope it helped!

  • userxyz
    userxyz ✭✭✭

    Thank you for the feedback, Red should overwrite Yellow and Green. Any recommendations how to structure it better?

  • userxyz
    userxyz ✭✭✭

    still looking for some input here and possibility to build out the formula into one line rather than 2 tiers.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    @userxyz

    If you want to have both formulas on 1 line, then you'll have to determine which lines are children from parents, which will be our first statement, and is a simple COUNT formula.

    =IF(COUNT(CHILDREN())=0, {Children formula}, {Project/Initiative Formula}).

    Project/Initiative formula, according to what you said should be this:

    =IF(COUNTIF(CHILDREN(), "Red")>0, "Red", IF(COUNTIF(CHILDREN(), "Yellow")>0, "Yellow", IF(COUNTIF(CHILDREN(),"Green")>0, "Green")))

    Children formula should be like this;

    =IFERROR(IF(Finish@row - TODAY()<0, "Red", IF(OR(Finish@row - TODAY()<3, Status@row = "On Hold", Status@row = "At Risk"), "Yellow" IF(Finish@row - TODAY()>3, "Green", IF(Status@row="Complete", "Blue")))), "")

    The IFERROR will take care of the case if Finish is blank or is not a date here.

    So mixing it up would give this:

    =IF(COUNT(CHILDREN())=0, IFERROR(IF(Finish@row - TODAY()<0, "Red", IF(OR(Finish@row - TODAY()<3, Status@row = "On Hold", Status@row = "At Risk"), "Yellow" IF(Finish@row - TODAY()>3, "Green", IF(Status@row="Complete", "Blue")))), ""), IF(COUNTIF(CHILDREN(), "Red")>0, "Red", IF(COUNTIF(CHILDREN(), "Yellow")>0, "Yellow", IF(COUNTIF(CHILDREN(),"Green")>0, "Green"))))

    Let me know if I got it as you want it to act.

    Hope it helped!

  • userxyz
    userxyz ✭✭✭

    Thank you David!

    For some reason I am getting an #UNPARSEABLE error when i use the formula.

  • userxyz
    userxyz ✭✭✭

    screenshot to the above comment

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @userxyz

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • userxyz
    userxyz ✭✭✭

    @Andrée Starå Hi thanks for reaching out - I just shared the Smartsheet with you.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @userxyz

    Glad we got it working!

    Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Alternative to using Children - would be using Descendants. That way if someone accidentally overwrites a project level with an direct use of a single health indicator - it will still roll up the project with all children, grandchildren and great grandchildren if they exist.

    Your Project Summary Formula would be:

    =IF(COUNTIF(DESCENDANTS(), "Red") > 0, "Red", IF(COUNTIF(DESCENDANTS(), "Yellow") > 0, "Yellow", IF(COUNTIF(DESCENDANTS(), "Green") > 0, "Green")))

    Thank you for this thread! It got me going in the right direction for what I needed - and I thought the Descendants idea was worth adding as an option for those of us who have lists with more levels. :D

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!