Formula to Capture (R / Y / G) Status Change

Karisa Ely

Hello,

I have 4 cells that should trigger the Close cell to change color. The Close cell should stay RED unless ALL 4 cells are GREEN.

I can get the formula to show Green or Red but it will not change based on the 4 cells. What am I missing?

Formula 1:

=IF(COUNTIF(CHILDREN([2]1:[5]1), "Green") = COUNT(CHILDREN([2]1:[5]1)), "Green", "Red")

Formula 2: Gets Close to be Red, but will not stay red until all cells are Green.

=IF([2]1 = "Green", "Green", IF([3]1 = "Green", "Green", IF([4]1 = "Green", "Green", IF([5]1 = "Green", "Green", "Red"))))

Kyle Chipman

You can use =IF(AND(... to stack up your conditions, as follows:

=IF(AND([2]@row = "Green", [3]@row = "Green", [4]@row = "Green", [5]@row = "Green"), "Green", "Red")

Also, it's a good habit to use @row whenever building out row-consistent formulas. Cuts down sheet load time, over time, and makes copying formulas stress-free should any references get mis-aligned, for whatever reason

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Let me know if you have any questions, and best of luck!

Kyle Chipman

You can use =IF(AND(... to stack up your conditions, as follows:

=IF(AND([2]@row = "Green", [3]@row = "Green", [4]@row = "Green", [5]@row = "Green"), "Green", "Red")

Also, it's a good habit to use @row whenever building out row-consistent formulas. Cuts down sheet load time, over time, and makes copying formulas stress-free should any references get mis-aligned, for whatever reason

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Let me know if you have any questions, and best of luck!

Karisa Ely

That works perfectly! Thank you so much and thanks for the link too. I was not aware of the @row feature so will be reading up on it!