Automating RYG And CHILDREN Formula

Mark Baldwin

Hi 

 

I am looking for a formula that will show in the parent an RYG colour based on the children.

 

If there are any Red in the children, I want to show Red in the parent,

If there are no Red but there are Yellow in the children, Show Yellow in the parent.

If there are no Red or Yellow but there is Green in the children, Show Green in the parent.

If there are no Red, Yellow or Green but there are Blue in the children, Show Blue in the parent.

 

I am completely stumped on what formula I need and if this is even possible. 

 

Thanks in advance

Mark

J. Craig Williams

Mark,

 

Try this:

 

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "NONE"))))

 

Hope this helps.

 

Craig

Mark Baldwin

Hi Craig

 

That worked like a charm! You saved me a lot of headache. 

 

Mark

J. Craig Williams

Glad to help Mark.

 

Craig

Rod McCracken

My children RYG is in a different column.  How would I vary the formula above to read the other column data?

J. Craig Williams

Columns are referenced like this:

 

[Column Name]:[Column Name]

 

If the column name has no spaces or special characters, Smartsheet will usually change it to this:

 

ColumnName:ColumnName

 

but I always type the [].

 

If you want to reference a set of rows, then you'd add the row numbers

 

[Column Name]23:[Column Name]32

 

for rows 23 to 32 inclusive.

 

There is also absolute referencing

[Column Name]$23:[Column Name]$32

 

Check out the help articles if that is needed.

 

Hope this helps.

 

Craig

LindseyC

Hello! 

 

I am working on creating a variation of this formula, but can't get it to work. 

 

If there are any Red OR Yellow in the children rows, I want the parent row to show Yellow. 

If there are only greens (no other color), I want the parent row to show Green. 

 

How do I do the "or" statement? Thanks for any help! 

 

Lindsey 

Rod McCracken

Hi LindseyC

In my formula below, using RYG formatting, if there are no children the cell stays blank, if there is a red or yellow child, show yellow, or if there is a green child show green. 

 

=IF(COUNT(CHILDREN([Action Status]48)) = 0, "", IF(OR(COUNTIF(CHILDREN([Action Status]48), "Red") > 0, COUNTIF(CHILDREN([Action Status]48), "Yellow") > 0), "Yellow", IF(COUNTIF(CHILDREN([Action Status]48), "Green") > 0, "Green")))

 

Hope this helps

Rod

LindseyC

Hi Rod! Yes, this is very helpful! Thanks for showing me how to add the "or" statement. Makes it very easy.