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

Find more posts tagged with

Formulas

Comments

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.

COUNTIF formula.jpg

Master sheet roll up.jpg

Ref P1 health.png

Ref P2 health.png

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…