IF statement with multiple values changing symbols

Options

I have a bunch of columns that have different check boxes to signify if certain paperwork is completed. I have figured out how to change values on the symbols by creating an extra switch column for the color dot symbols to appear, when one of the boxes are checked. My issue is, I need to have the colors change as values are added to the smartsheet. My first switch, is when text is submitted, the color changes - signifying red, while there is text, there are about six other check boxes that need to be checked (and I am using IF(OR ) wanting the color to change (green) when any one of the boxes is check marked. When another specific check box is checked - while all the others are checked, I want the symbol to change another color. To top it off, Not matter if all of these fields are full, if another specific box gets check marked, I want the symbol to change (blue). See the IF statement is below.


From my switch column

=IF([ProductName]1 > "", "1", IF(OR([DOC1]1, [DOC2]1, [DOC3]1, [DOC4]1, [DOC5]1, [DOC6]1,) = "true", "2", IF([DELIVERED]1 = "true", "3", IF([On-Hold]1 = "true", "4"))))

I have the Symbol column reading the numbers,

1 - Red

2 - Yellow

3 - Green

4 - Blue


When ProductName is typed, it goes red and will not change colors if any check box is checked; vice versa when everything is blank. I would like the numbers to update when values are added along the way. (4 - Blue) to bypass overwriting all colors. Basically using if, while loops.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!