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!

Find more posts tagged with

Accepted answers

All comments

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

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

userxyz

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

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

Thank you David!

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

userxyz

screenshot to the above comment

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!

userxyz

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

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!

catvarn

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.