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.

Setting conditions for RYG balls

Anthony Lunn
Anthony Lunn ✭✭
edited 12/09/19 in Archived 2017 Posts

I would like to have the RYG balls automatically adjust based on another status column. For instance, if my outside sales people set the status of a row from bidding to the following:

Status "Lost" = RED

Status "Postponed" or "Unknown" = YELLOW

Status "Won" or "Obtained" or "Negotiating" = GREEN

Status "Bidding" =Will remain BLUE which is default and initial status


  • L_123
    L_123 ✭✭✭✭✭✭


  • Thanks Luke, that was quick!

    I am new to formulas and SS all together. Would I paste this formula into the Ball-cell?

  • L_123
    L_123 ✭✭✭✭✭✭

    Yes. I listed Status as your column and 1 as your row references. Change every occurrence of those to suit your sheet and post the formula to the column with the balls. 

    Hint, if you manually post all options of any symbol reference and set the cells next to the symbol equal to the symbol, it will output text. 



  • I am getting an error (cell 1) when pasting the code. I changed the verbage from "Status" to "Sales Stage" since that is what I used in mine. 


    =if(Sales Stage1="Lost","Red",if(or(Sales Stage1="Postponed",Sales Stage1="Unknown"),"Yellow",if(or(Sales Stage1="Won",Sales Stage1="Obtained",Sales Stage1="Negotiating"),"Green","Blue")

    What are your thoughts?


  • L_123
    L_123 ✭✭✭✭✭✭

    If there is a space in the column name you need to put it in brackets so the system knows to look at it as a single name.

    =IF([Sales Stage]1 = "Lost", "Red", IF(OR([Sales Stage]1 = "Postponed", [Sales Stage]1 = "Unknown"), "Yellow", IF(OR([Sales Stage]1 = "Won", [Sales Stage]1 = "Obtained", [Sales Stage]1 = "Negotiating"), "Green", "Blue")))

    Hint, since I made mine editable you can change the name of the column and it will update all references to the new name. I changed mine to your column name so you can see it in the sheet I posted previously.

  • This is perfect. You are the man!

    Since you seem to be very fluent in the formula coding-I have one for you to wrap your head around. 

    On the same sheet: How can I have an Update Request automatically go out (x) days after the "Bid Date" has passed? Specifically to the pertinent "Sales Rep". In a perfect world,I would like to be able to load the data and the "Follow-up" Update Request would go out when the trigger date passed. Any ideas? That is, for the most part, the premise of this sheet so it would make my life a little easier to automate it. 

  • L_123
    L_123 ✭✭✭✭✭✭

    Create a red flag column and use this formula.

    =IF(NETDAYS([Bid Date]1, TODAY()) >= 3, 1, 0)

    It will flag the row if the bid date is more than 3 days old. Then use a notification to send an email if the flag is posted. I don't believe you can send an actual update request, but you can play around with posting hyperlinks inside of the notification, and other little tricks.

This discussion has been closed.