Automating RYG balls

Nicolle

=IF ISBLANK([Due]6), "Red", ["Yellow"]),=IF(DONE5 = 1, "Green")

Not working.. . . Help

J. Craig Williams

Nicole,

You are referencing cell Due6 and DONE5. Probably not what you want. They might need to point the same row (Due6 and DONE6) where 6 is the row number

Something like this:

=IF(ISBLANK([Due]6), "Red",IF(DONE5 = 1, "Green", "Yellow"))

I wrote that but did not test it. 

Craig

 

Yessica

Hi Craig, 

I am trying to get my RYG Ball Column to populate on these conditions:

1) Turn Red if the RFQ Returned date is past the RFQ Due Date at 7 days

2) Turn Yellow if RFQ Due Date at 7 days is TODAY

3) Turn Green if RFQ Returned Date was before RFQ Due Date at 7 days. 

 

Here's the formula that I came up with, but it is not working. 

 

=IF([RFQ Returned]411 < ([RFQ Due at 7 days](), "Green", IF([RFQ Due at 7 days]411 = TODAY(), "Yellow", IF([RFQ Returned]411 > RFQ Due at 7 days(), "Red")))

 

J. Craig Williams

I cleaned up the formula to remove syntax errors:

=IF([RFQ Returned]411 < [RFQ Due at 7 days]411, "Green", IF([RFQ Due at 7 days]411 = TODAY(), "Yellow", IF([RFQ Returned]411 > [RFQ Due at 7 days]411, "Red")))

You'll need to verify if it is doing what you want it too.

The errors included extra parentheses and mal-formed cell references.

Craig

Yessica

The first part of the formula worked, but not the others. 

Yessica

Got it working but forgot to include a condition. 

If RFQ Returned is blank RYGG Ball should be Grey. 

 

 

rhilton36656

I have what I think is a very simple request (some of what I've read on here are very complicated and way over my head.)

I just want the RYB ball to change color based on words in another column.

I've tried this formula =IF([Status@row]=Not Needed, "Green","Red"), but it comes back with #UNPARSEABLE.  I'm sure there is something very basic I'm missing.

Help please?

Thank you!

 

 

Margaret.clarkson

I'm attempting to set up several nested IF & IF/AND statements to account for defined due date and whether or not the item has been delivered.

Green - if due more than a week away

Yellow - if due within 7 days and not already delivered

Red - if due date is in the past and not already delivered

Here is the formula I've created. The pieces work individually, but I get the "#Incorrect Argument Set" message when I input the entire formula.

What am I missing?

 

=IF([Date Due]16 - TODAY() > 7, "Green", IF(AND([Date Delivered]16 = " "), ([Date Due]16 - TODAY() <= 7), "Yellow", IF(AND([Date Delivered]16 = " "), ([Date Due]16 > TODAY()), "Red")))

clairee42221

Hi, 

Im having some similar issues and was hoping someone could help improve on my formula 

Im trying to create a HEALTH flag marked "Green" if the task is not due today, "Yellow" if the task is due today, and "Red" if the task is past due.

However, the HEALTH flag should stay blank if: 



1) the STATUS column is "complete"

2) the FINISH column is blank

 

I attached a picture of the formula i'm currently using. Thank you in advance for the help! 



 

clairee42221

Also see formula here 

=IF(Finish6 < Today6, "Red", IF(Finish6 = Today6, "Yellow", IF(Finish6 > Today6, "Green")))

Nicole Stewart

Similar Issue: RYGB

I want the status balls to automatically update based on the due date - but I also want the status to go blue after the "Item Completed" column is checked.

Ie.

If End Date is in the past or due within the next 7 days = Red

If End Date is Due within the two weeks = Yellow

If end Date is Due in three weeks or more = Green

If "Item Complete" is Checked = Blue

Gsmith

Hi Travis

I am just setting up a tracker in smartsheet and would like to automate the RYG balls. I have the following formula but would like to edit it so that if a date is added to the Completed Date column the ball goes grey or a different colour, is this possible?

 =IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(3) >= [Due Date]@row, "Yellow", "Green"))

Thanks

Gill

sheadlee74661

i am trying to get a format to work as such. I have a date cell...and i want it to turn red with bold black text...if it is greater than the planned date which is a T-Minus date...and i can't seem how to figure it out...anyone have anything ??

Andrée Starå

Hi Sheadlee,

Try this.

You'll need a so-called helper column for this to work.

Add a check box helper column named Helper with the following formula:

=IF(DateCell@row > PlannedDate@row; 1)    

The same version but with the below changes for your and others convenience.    

=IF(DateCell@row > PlannedDate@row, 1) 

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Next step is to structure the conditional formatting rule (please see attached screenshot)

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

varina.samuels85501

Hi Travis, your formula worked great, thank you. I have a "Status" and "Status Update" column. I put your formula in "Status Update" column. The "Status" column is a dropdown for not started, in progress, on hold, and complete. How do I add to your formula to only use RYG balls if the "Status" is not started or in progress?