Task Health, error applying formula
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
Best Answer
-
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.
Answers
-
=IF(AND([Column1]@row = 1, [Column2]@row = 1, [Column3]@row = 1, [Column4]@row = 1), "green")
-
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
-
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.
-
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.
-
Wonderful, pleased you have what you need.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!