#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Automating RYG balls

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

Not working.. . . Help

• ✭✭✭✭✭✭

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

• 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")))

• ✭✭✭✭✭✭

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

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

• Got it working but forgot to include a condition.

If RFQ Returned is blank RYGG Ball should be Grey.

• 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.

Thank you!

• 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")))

• edited 04/16/18

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!

• Also see formula here

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

• 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

• 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

• 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 ??

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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?

This discussion has been closed.