Trying to Figure Out Formulas for Health/Status/%Complete

Gabbie

Hi, I have three formulas given to me from a smartsheet rep in smartsheet 101 training that aren't working together. Receiving an "Unparseable" error message.

Status Updates with % Complete 

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", "In Progress")) 

Health = Status

=IF(Level@row > 2, IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "Complete", TODAY() > [End Date]@row), "Red", "Yellow")))

=COUNT(ANCESTORS()) + 1

I wasn't really given instructions on how to use multiple formulas in a cell, please advise. Not sure how to make it work. I was trying to put them all under the "Status" column.

Paul Newcome

Ah. Ok. So you don't need that third one, and you can adjust your RYG to this:

=IF(Status@row = "Complete", "Green", IF(TODAY() > [End Date]@row, "Red", "Yellow"))

Paul Newcome

Can you describe exactly how they are supposed to be working together and what the expected output / logic is?

It looks to me like you have three separate formulas for 3 separate columns and they aren't designed to be nested together into one single formula.

Screenshots would also be helpful.

Gabbie

The first formula was given to me for the status column to update based on % complete.

The second formula was given to me so that the health column symbol would update based on the status. However, she gave me a second formula to loop in with this formula to make it work. It wasn't very helpful and I'm not sure what the solution is because I've only been using Smartsheet for a month.

I want the status to update based on % complete and I also want the health symbol to update based on status.

I'm not sure how to accomplish this as the smartsheet rep was not very helpful and told me to reach out to support. Thanks!

Paul Newcome

Ah. Ok. So you don't need that third one, and you can adjust your RYG to this:

=IF(Status@row = "Complete", "Green", IF(TODAY() > [End Date]@row, "Red", "Yellow"))

Gabbie

Thank you! Very helpful!

Paul Newcome

Happy to help. 👍️