RYG status for it to be blank (no ball) in column

laura.sandoval

Hello I need some help with a formula.

I have a status column that is linked to the "status of contract" column and depending on the status of the contract, the balls will be green, red or yellow. Issue I'm having is for it to be blank (no red ball) whenever the status of contract is blank. This is the formula I currently have:

Yellow = Completed

Red = Cancelled

Green = Active or Ongoing

=IF([Status of Contract]@row = "Ongoing", "Green", IF([Status of Contract]@row = "Active", "Green", IF([Status of Contract]@row = "Cancelled", "Yellow", "Red”)

Help!

Dianna

You have yellow tied to cancelled. Try

=IF([Status of Contract]@row = "Ongoing", "Green", IF([Status of Contract]@row = "Active", "Green", IF([Status of Contract]@row = "Completed", "Yellow", IF([Status of Contract]@row = "Cancelled", "Red”, "")

laura.sandoval

Oh you're right I did woops! It still says unparseable though :( this is what I have so far

Yellow = Completed

Red = Cancelled

Green = Active or Ongoing

FORMULA:

=IF([Status of Contract]@row = "Ongoing", "Green", IF([Status of Contract]@row = "Active", "Green", IF([Status of Contract]@row = “Completed”, "Yellow", =IF([Status of Contract]@row = “Cancelled”, "Red”, “”)

John Drugan

One detail, I see an unnecessary "=" in front of the final "IF" statement of your 10:34am post.

Paul Newcome

There are a number of places where you have invalid characters. See the slanted quotes as opposed to the quotes that are straight up and down? Those are called "smart quotes" which (ironically enough) are not valid characters within a Smartsheet formula. You would need to retype the formula here, in Smartsheet itself, or in a text editor such as Notepad (not Word).

Here is my take on the formula (with quotes typed here so you don't have to worry about them).

=IF(OR([Status of Contract]@row = "Ongoing", [Status of Contract]@row = "Active"), "Green", IF([Status of Contract]@row = "Completed", "Yellow", IF([Status of Contract]@row = "Cancelled", "Red")))

laura.sandoval

Wow I had no idea that was a thing! That was giving me issues, thank you for sharing that with me.

I have one more question, the formula worked thankfully! How can I add something for the red ball? I want to add the word "Blank" for "Red".

=IF(OR([Status of Contract]@row = "Ongoing", [Status of Contract]@row = "Active"), "Green", IF([Status of Contract]@row = "Completed", "Yellow", IF([Status of Contract]@row = "Cancelled", IF(Status of Contract]@row = "Blank", "Red")))

I tried this and it said unparseable again...any ideas why?

Paul Newcome

The error itself is from a missing open square bracket there before the very last cell reference.

I'm not sure I follow what you mean about the blank bit though. Do you mean if the [Status of Contract]@row is blank (that's the one missing the square bracket by the way) or if it is "Cancelled" then output "Red"? If so:

=IF(OR([Status of Contract]@row = "Ongoing", [Status of Contract]@row = "Active"), "Green", IF([Status of Contract]@row = "Completed", "Yellow", IF(OR([Status of Contract]@row = "Cancelled", [Status of Contract]@row = ""), "Red")))