Formula for RYG Symbols in Child Rows to Update Parent Row

SteCoxy

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?

Jeff Reisman

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.

Jeff Reisman

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.

SteCoxy

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 :-)