Automate RYG Ball based on Checkboxes in Other Columns

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?

Find more posts tagged with

Healthcare

Formulas

Support

IT & Operations

Project Management

Comments

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