7 Replies Latest reply on Nov 15, 2018 1:39 PM by Paul Wachtler

    Unable to change cell color based on calculated field

    Anjali Sn

      I have two columns called "GoalSign" which has values like '>', '=', '>=', '<', <=' & Null and a "GoalCalc" column which has numeric values like 1,2,3,4,5,... Now I want to color the background of the fields [W1]..[W6] (these fields have numeric values as well) based on the GoalSign & GoalCalc fields. Like if GoalSign is '=' & GoalCalc is 10, and if the value in any of [W1],..[W6] is 11, then the color should be Green else Red.

       

      I have created a calculated field called "GoalColorCalc" for this as follows:

       

      IF [GoalSign] = '='

      THEN

      IF ([W1] = [GoalCalc]) OR ([W2] = [GoalCalc]) OR ([W3] = [GoalCalc]) or ([W4] = [GoalCalc]) or

      ([W4] = [GoalCalc]) OR ([W5] = [GoalCalc])

      THEN

      "True"

      ELSE

      "False"

      END

      ELSEIF [GoalSign] = '>='

      THEN

      IF ([W1] >= [GoalCalc]) OR ([W2] >= [GoalCalc]) OR ([W3] >= [GoalCalc]) or ([W4] >= [GoalCalc]) or

      ([W4] >= [GoalCalc]) OR ([W5] >= [GoalCalc])

      THEN

      "True"

      ELSE

      "False"

      END

      ELSEIF [GoalSign] = '<'

      THEN

      IF ([W1] < [GoalCalc]) OR ([W2] < [GoalCalc]) OR ([W3] < [GoalCalc]) or ([W4] < [GoalCalc]) or

      ([W4] < [GoalCalc]) OR ([W5] < [GoalCalc])

      THEN

      "True"

      ELSE

      "False"

      END

      ELSEIF [GoalSign] = '>'

      THEN

      IF ([W1] > [GoalCalc]) OR ([W2] > [GoalCalc]) OR ([W3] > [GoalCalc]) or ([W4] > [GoalCalc]) or

      ([W4] > [GoalCalc]) OR ([W5] > [GoalCalc])

      THEN

      "True"

      ELSE

      "False"

      END

      ELSEIF [GoalSign] = '<='

      THEN

      IF ([W1] <= [GoalCalc]) OR ([W2] <= [GoalCalc]) OR ([W3] <= [GoalCalc]) or ([W4] <= [GoalCalc]) or

      ([W4] <= [GoalCalc]) OR ([W5] <= [GoalCalc])

      THEN

      "True"

      ELSE

      "False"

      END

      END

       

      Next I placed this calculated field on Color, but its not changing the colors of those fields. Where am I going wrong???

       

      Attached is the screenshot.