I have a formula that looks like this:
I'm trying to assign specific colors to different disciplinary actions and highlight the entire cell. Here's my formula:
IF ATTR([New Hire]) = "N" THEN
IF SUM([Credit Used]) IN (6, 6.5, 7, 7.5) THEN "Verbal Coaching"
ELSEIF SUM([Credit Used]) >= 10.5 THEN "Term Review"
ELSE ""
END
ELSEIF ATTR([New Hire]) = "Y" THEN
IF SUM([Credit Used]) = 1 THEN "Warning"
ELSEIF SUM([Credit Used]) = 1.5 THEN "Written Warning"
ELSEIF SUM([Credit Used]) = 2 THEN "Final Written Warning"
ELSE ""
END
ELSE ""
END
I tried my CASE statement for colors:
CASE [Disciplinary Action]
WHEN "Verbal Coaching" THEN "Yellow"
WHEN "Warning" THEN "Light Grey"
WHEN "Written Warning" THEN "Light Grey"
WHEN "Final Warning" THEN "Light Red"
WHEN "Term Review" THEN "Light Red"
ELSE ""
END
I was expecting to have specific colors per each Disciplinary Action solid per each cell row. Not only the word letters in color.
How can I ensure the entire cell is highlighted with the specified colors and correctly handle the splitting of 'N' and 'Y' values?
Thank you!
You don't need the case statement.
Just drag the Disciplinary Action field to the Color shelf/button on the marks card. Then assign the colors you like by editing the colors. The easiest way to do that last step is to double click on a color tile in the color legend.
Also, do you have more than one data row per person? If not, then you can simplify by dropping the ATTR() and SUM() calls.