RYG Parent Changes with Daughter Scheduled Health Change
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!
Best Answer
-
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.
Answers
-
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.
-
O my goodness that is so good and so quick! You took less time to respond than I had spent all afternoon trying to figure it out! TY! It's already in the sheet and working great!! Blessings
-
Happy I could help you out @Rod Sanderson
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!