#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Automating RYG And CHILDREN Formula

Options
edited 12/09/19

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.

Mark

• ✭✭✭✭✭✭
Options

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

• Options

Hi Craig

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

Mark

• ✭✭✭✭✭✭
Options

Craig

• edited 06/07/16
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• Options

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

• edited 06/23/16
Options

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

This discussion has been closed.