Formula for RYG Symbols in Child Rows to Update Parent Row
Hello, I'm wondering if there's a formula that could automatically update the parent row in a column that has RYG symbols?
The parameters would be:
-Green overall (in the parent row) if all children rows have green symbols?
-Yellow overall (in the parent row) if any children rows have at least 1 yellow symbol?
-Red overall (in the parent row) if any children rows have at least 1 red symbol?
In addition, I'm wondering if it would be possible to apply this formula to the column, so that the users of the sheet wouldn't have to apply this to each parent row each time a new parent row was created?
Best Answer
-
Place this in the RYG column in each parent row. Replace "RYGColName" with the actual RYG column name.
=IF(CONTAINS("Red", CHILDREN(RYGColName@row)), "Red", IF(CONTAINS("Yellow", CHILDREN(RYGColName@row)), "Yellow", IF(CONTAINS("Green", CHILDREN(RYGColName@row)), "Green", ""))
What this does is look into all the RYG children cells to evaluate if the color is present in the range of cells. If it finds red in any of them, the ball should be red. If it doesn't find red, check for yellow, and if it finds yellow, the ball should be yellow. If there's no yellow either, check for green and if it's there set the ball to green. If it doesn't find any of the three colors, leave the cell blank.
You can't convert it to a column formula in the RYG column because it would override the ball selection dropdown in all the children cells. You can, however, place it in it's own RYG column, such as "RYGParent", and that would work fine as a column formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Place this in the RYG column in each parent row. Replace "RYGColName" with the actual RYG column name.
=IF(CONTAINS("Red", CHILDREN(RYGColName@row)), "Red", IF(CONTAINS("Yellow", CHILDREN(RYGColName@row)), "Yellow", IF(CONTAINS("Green", CHILDREN(RYGColName@row)), "Green", ""))
What this does is look into all the RYG children cells to evaluate if the color is present in the range of cells. If it finds red in any of them, the ball should be red. If it doesn't find red, check for yellow, and if it finds yellow, the ball should be yellow. If there's no yellow either, check for green and if it's there set the ball to green. If it doesn't find any of the three colors, leave the cell blank.
You can't convert it to a column formula in the RYG column because it would override the ball selection dropdown in all the children cells. You can, however, place it in it's own RYG column, such as "RYGParent", and that would work fine as a column formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, thanks for coming back to me and confirming this. I will run it by the team and get their thoughts. It will certainly be useful for future instances :-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!