Formula Question/ Help Needed

Jason H

I currently have the following Formula updating a STATUS (Not Started, Complete, IN Progress) based on % Completed. Works flawlessly

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started"))

I want to take this to another level. As I have another column "Health" using symbols (Green, Yellow, Red, Blue) each identifying a tasks progress (Green = completed, Blue - In progress/On time, Yellow - 3 days before due/not 100%, and Red = missed due date/ not 100%

=IF([% Complete]492 = 1, "Blue", IF(TODAY() - Finish492 > -1, "Red", IF(TODAY() - Finish492 > -3, "Yellow", "Green")))

I want to take the FIRST formula to the next level, by having it recognize when a task is RED or Past Due and so it would flip the STATUS to, Past Due. And this is where I'm stuck!

So it would be this working STATUS working formula + referencing the HEALTH column, when RED.

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started"))

My failed Attempt.................

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started", IF Health01 = RED, "Past Due"))

Problem is, this doesn't work.... :(

hollyconradsmith

Hi @Jason H ,

Have you considered using cell update automation to accomplish this? It may be a simpler solution for you! https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow

Jason H

@hollyconradsmith - Thanks for this tip! I had never thought about using this nor knew it was available. Sadly, when using this feature it wipes clean the original formula and that is not what we want to do.

We want to highlight the PAST DUE tasks via changing the STATUS identifier - but once they adjust due dates, it go back to tracking using the original formula.

Any thoughts there?