Formulas
Hi, can anyone help please.
I'm trying to change formula that changes the colours in the health column automatically when you change the drop down in State column such as complete, in progress. I would like to have the following
Green – not started
Yellow – in progress
Red – when it goes past the due date
Blue – Complete
There is already a formula in the smartsheet but I’m struggling to change it. Any help would be great. Thanks
Comments

Hi,
Can you paste here the formula you are currently using?

Try this formula. You may need to replace the column names status or Due Date with your actual column names. If your actual column names contain spaces or end in numbers surround your column name in brackets [ ].
=IF(Status@row= "Not Started", "Green", IF(status@row = "In Progress", "Yellow", IF([Due Date]@row < Today(), "Red", IF(status@row = "Complete", "Blue"))))

thanks for your help Mike but that didn't work. I'm quite new to IF formulas so will keeping trying different things.

=IF(Progress5 = "Complete", "Blue", IF(AND(Progress5 <> "Complete", TODAY() > [End Date]5), "Red", IF(AND(Progress5 = "Not Started"), "Green", "Yellow")))

Other than an unnecessary AND function towards the end, your syntax is correct.
=IF(Progress5 = "Complete", "Blue", IF(AND(Progress5 <> "Complete", TODAY() > [End Date]5), "Red", IF(Progress5 = "Not Started", "Green", "Yellow")))
.
What is the current outcome of your formula, and what is the desired outcome? Are you getting any errors or is it just not producing the expected results?

Ah, I see. I used the STATUS as the column name instead of Progress. Try Paul's rewrite of your formula or take mine and replace STATUS with PROGRESS.

Thank you, all sorted now

thanks all sorted now

I'm working on the Master roll up sheet which feeds into a dashboard. I would like to report on the P1 health from 10 sheets not just one sheet. See attached screen shots. When i change the sheet for P1 health or P2 health it just reports the health from one sheet.


You would need to pull each sheet separately and then compile them. You could either create a cell for each sheet to house the individual formulas, or you can write out each formula and add them together in a string.
P1 formula
P2 formula
P3 romula
.
or
.
P1 formula + P2 formula + P3 formula

Thanks Paul, i'm not sure i understand what you mean

I've got it now, thanks for your help
Help Article Resources
Categories
Check out the Formula Handbook template!