Setting conditions for RYG balls

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

Find more posts tagged with

Sales

Sharing and Collaboration

Formulas

Project Management

Update Requests

Comments

L_123

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

Anthony Lunn

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

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.

https://app.smartsheet.com/b/publish?EQBCT=94043c7d90da440d808dc24c5f24f068

Anthony Lunn

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

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.

Anthony Lunn

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

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.