RYG Parent Changes with Daughter Scheduled Health Change

Rod Sanderson

Dear Smartsheet Team

I am trying to get Parent [Scheduled Health to reflect 'worst color' from daughter lines.

The logic:

if any one of Line 3 or Line 16 or Line 20 (of the [Scheduled Health] Column is Red then Line 1 is Red

If any one of line 3, 16, or 20 is yellow then line 1 is yellow

if all line 3, 16, 20 green then line 1 green

I am trying to get line one that I pull for reports to alert if anything in the project is going late?

Thank you!

KPH

Let's start with if any of rows 3, 16, or 20 are red.

You can put this formula into row 1

=IF(OR([Schedule Health]3 = "Red", [Schedule Health]16 = "Red", [Schedule Health]20 = "Red"), "Red")

Then we can add a part to say if this isn't true but all rows are green, then use a green symbol. Add this part in bold.

=IF(OR([Schedule Health]3 = "Red", [Schedule Health]16 = "Red", [Schedule Health]20 = "Red"), "Red", IF(AND([Schedule Health]3 = "Green", [Schedule Health]16 = "Green", [Schedule Health]20 = "Green"), "Green"))

Then we add a part to say if neither of these things is true, then use the yellow symbol. Add this part in bold.

=IF(OR([Schedule Health]3 = "Red", [Schedule Health]16 = "Red", [Schedule Health]20 = "Red"), "Red", IF(AND([Schedule Health]3 = "Green", [Schedule Health]16 = "Green", [Schedule Health]20 = "Green"), "Green", "Yellow"))

Note - this is based on the health of particular rows using the row number and does not use the parent/child relationship. If rows 3, 16, and 20 are the only children of row 1 you can amend this to use that relationship rather than the row number, which will give you greater flexibility to add and remove rows. If this is the case, please share a screenshot. From the snippet above, it does not look like row 3 is a child of row 1.