Task Health, error applying formula

Laura G

Hi All

I have 4 columns on a sheet which are checked boxes, when they are all ticketed, i want the task health column to change to green. I am using this formula and applied it to the task health column but I am getting a circular reference. Where should i be applying to formula or is the formula wrong?

=IF(AND([Column1]@row = 1, [Column2]@row = 1, [Column3]@row = 1, [Column4]@row = 1), "green", [Task Health]@row)

Thank you

Laura G

Perfect! Thanks making the "G" capital is exactly what i was missing. The health icon now turns to the green dot. Thanks for all of your help.

BullandKhmer

=IF(AND([Column1]@row = 1, [Column2]@row = 1, [Column3]@row = 1, [Column4]@row = 1), "green")

Laura G

Thanks, this is what i was getting before. The formula is applied to the task health column but it doesn't change the colour, only adds the word

KPH

Hi Laura

If you want to change the cell background color you do not need a formula. You need to use conditional formatting.

Click on this icon

Then click on Add New Rule.

Click on Set Condition and set up the first condition.

Then click on this format, select the background icon and then green.

Then click on entire row and change this to just the cell you want to change

Now add additional clauses to the condition using the drop down arrow and Add Condition

Click on set condition again and add the requirements for column 2

Repeat for 3 and 4 and you will have a rule like this:

Option 2

Alternatively, you could use an icon within the cell rather than changing the cell background color. You cannot add any text to these columns. Just symbols. But you can do it with a formula.

To do that, change the format of the Task Health column using Edit Column Properties and make it a symbol column. Select the icon set you prefer.

Now put your formula in the Task Health column. You do need to capitalize the G in green for it to be detected.

=IF(AND([Column1]@row = 1, [Column2]@row = 1, [Column3]@row = 1, [Column4]@row = 1), "Green")

This will add a green dot when the formula evaluates to True.

Laura G

Perfect! Thanks making the "G" capital is exactly what i was missing. The health icon now turns to the green dot. Thanks for all of your help.

KPH

Wonderful, pleased you have what you need.