Automate RYG Ball based on Checkboxes in Other Columns

Ray Rios

I need help from the SmartSheet formula masters...

 

I have 10 checkbox columns (checkbox) and I want the RYG ball to automatically change with the following conditions:

• 1) "Red" if no checkboxes are checked.
• 2) "Yellow" if some checkboxes are checked.
• 3) "Green" if all checkboxes are checked.

 

with one exception... if a check box is "not required" (i.e., the cell value is "-" or "N/a") it should be excluded from the countif formula.

 

The formula =IF(COUNTIF([Column1]1:[Column2]1, 0) > 0.5, "Red", "Green") works for half of the request, but doesn't help with the "Yellow" status.

 

What can I do?

Brett Evans

this gets everything except the exception,  I'll have to think about that.

 

 

=IF(AND([Column1]1 = 1, [Column2]1 = 1, [Column3]1 = 1), "Green", IF(OR([Column1]1 = 1, [Column2]1 = 1, [Column3]1 = 1), "Yellow", "Red"))

 

 

https://app.smartsheet.com/b/publish?EQBCT=e85397b4941d48b79122ffe39c19427e

Brett Evans

Thought about it...

 

You have to many columns to manage all of the exceptions with an accurate fomula.  I believe you have thousands or branches of an If statement that you would have to write to fully cover the ten columns.  i.e if col 1 and col 6 were NA but the rest were checkboxes, or if columns 1,2,3,6,7,8,9 were NA or "-" and the rest were checkboxes.

 

So I came up with this formula to cover if any columns had NA or "-"

=IF(OR(ISTEXT([Column1]1), ISTEXT([Column2]1), ISTEXT([Column3]1)), "Set Manually", IF(AND([Column1]1 = 1, [Column2]1 = 1, [Column3]1 = 1), "Green", IF(OR([Column1]1 = 1, [Column2]1 = 1, [Column3]1 = 1), "Yellow", "Red")))

 

When the condition "Set Manually" arises you would simply click in the RYG column and set the value yourself.  This has the drawback that this row would never update again.

 

I only covered columns 1 thru 3 in my formulas.  You would need to expand thelogic to your ten columns.

 

J. Craig Williams

Ray,

 

If your columns are sequential or the other columns in between do not have a 1, "-", or "N/a" value, then this might work:

 

=IF(COUNTIF([ChkBox1]23:[ChkBox10]23, 1) = 0, "Red", IF(COUNTIF([ChkBox1]23:[ChkBox10]23, 1) = (COUNT([ChkBox1]23:[ChkBox10]23) - COUNTIF([ChkBox1]23:[ChkBox10]23, "N/a") - COUNTIF([ChkBox1]23:[ChkBox10]23, "-")), "Green", "Yellow"))

 

My 10 check box columns are named ChkBox1, ChkBox2, ChkBox3, ... ChkBox10.

The formula is looking at the check boxes in row 23.

 

If no checkboxes checked - red.

If the number of checked boxes (=1) is equal to the total - count of "N/a" - count of "-", green

Otherwise, yellow.

 

Hope this helps.

Craig

Ray Rios

I think I might have made this sound more complicated than it needs to be. The "N/a" or "-" are synonomous. I will not use both, I will only use one or the other. I know the formular must include this first part "=IF(COUNTIF([Column1]1:[Column2]1, 0)" but I can't seem to get it to work as a nested formula.

J. Craig Williams

Just remove one

 

=IF(COUNTIF([ChkBox1]23:[ChkBox10]23, 1) = 0, "Red", IF(COUNTIF([ChkBox1]23:[ChkBox10]23, 1) = (COUNT([ChkBox1]23:[ChkBox10]23) - COUNTIF([ChkBox1]23:[ChkBox10]23, "N/a") - COUNTIF([ChkBox1]23:[ChkBox10]23, "-")), "Green", "Yellow"))

 

Craig

Brett Evans

Nice one!