Terminating a formula once criteria met

Options

Hi!


I am working on a sheet that has a basic if formula to populate the Red, Yellow, Green symbols. I want to be able to have the cell not change or have the formula run once the line is shown as "Red."


My formula is


=IF(AND([Scheduled End Date]1 < TODAY(), Status1 <> "Closed"), "Red", IF([Scheduled End Date]1 <> [Actual End Date]1, "Yellow", "Green"))


With how that is now, the status will change once the Status column is updated to "Closed," but I want it to stay red once it's red, even if the status has changed to closed.


I've thought of a work around to have different closed statuses that will automatically make them the color "Closed - Red" or "Closed - Yellow" will override the rest of the if statement, but ideally I'd like to be able to stop the formula from running once the criteria is met.


Is there any way to do that or another work around that would be more seamless?


Thank you!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!