Formulas

helen.hallam

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 

Jose da Silva

Hi,

 

Can you paste here the formula you are currently using?

Mike Wilday

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"))))

helen.hallam

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

helen.hallam

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

Paul Newcome

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?

Mike Wilday

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. 

helen.hallam

Thank you, all sorted now 

helen.hallam

thanks all sorted now 

helen.hallam

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.

 

 

Paul Newcome

Happy to help! 

Paul Newcome

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

helen.hallam

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

helen.hallam

I've got it now, thanks for your help