# Heath Status Symbol - Parent Row Setup

Options
✭✭

Hey Guys,

Brand new user here trying to understand the correct formula for a simple project health status.

Have new project where all children roll up to one parent row with start date, end date & % complete.

Would like to have a simple Red, Yellow & Green symbol based upon the parent row that shows overall project status based upon the parent Start , End Date & % Complete.

Can anyone help me in understanding how to set this up?

Hope this makes sense.

Samual

• ✭✭✭✭✭✭
Options

Try this:

=IF([% Complete]@row = 1, "Blue", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(10), "Yellow", "Green")))

• ✭✭✭✭✭✭
Options

So you want all of the child rows to match the parent row RYG? What would be the logic for populating the parent row RYG?

• ✭✭
Options

Hi Paul,

We just want the child rows to roll up the overall % Complete for the parent which they do.

Now I'm just looking for the RYG status for the parent overall % Complete to reflect the status of the project at a high level . We might add to all the children rows at a later time for more detail.

Thanks for the help !

Sam

• ✭✭✭✭✭✭
Options

If everything is rolling up then you should be able to apply a column formula so that every row is based on the criteria you are wanting for the RYG.

• ✭✭
Options

Hey Paul,

Here's what I'm looking for below, correct me if my logic is backwards. I looked at some examples and modified them to get parse errors.

·       COMPLETE - Blue: % Complete is 100% regardless of the date.

·       PAST DUE - Red: Today’s Date is 1 day after Due Date AND % Complete is < 100%

·       Behind - Yellow: Today’s Date is 10 before Due Date AND % Complete is < 75%

·       On Track - Green: Until its hits the Behind Yellow above.

Sam

• ✭✭✭✭✭✭
Options

Try this:

=IF([% Complete]@row = 1, "Blue", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(10), "Yellow", "Green")))

• ✭✭
Options

That worked !

Thank You

Sam

• ✭✭
Options

Hey Paul,

The formula worked, how can we modify it so that when the % complete is below 75% within 10 days shows behind, other wise anything above 75% shows on track regardless of the 10 day count?

• ✭✭✭✭✭✭
Options

So you want what exactly? Yellow only if within 10 days and less than 75%. If it is 76% and due tomorrow, it still shows green?

• ✭✭
Options

Hey Paul,

Not thinking through the process enough, the formula you provided will get us there.

Thank you again for ur help!

Sam

• ✭✭
Options

Hi Paul,

Could you look at the formula below and tell me what I missed, getting error.

Trying to get : Complete , Not Started , In Progress & Past Due

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row

= 0), "Not Started", "In Progress", IF[TODAY() > [Due Date]@row, "Past Due")))

Thank You

Sam

• ✭✭✭✭✭✭
Options

Try this:

=IF([% Complete]@row = 1, "Complete", IF([Due Date]@row< TODAY(), "Past Due", IF([% Complete]@row> 0, "In Progress", "Not Started")))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!