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
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
Comments
-
if(Status1="Lost","Red",if(or(Status1="Postponed",Status1="Unknown"),"Yellow",if(or(Status1="Won",Status1="Obtained",Status1="Negotiating"),"Green","Blue")
-
Thanks Luke, that was quick!
I am new to formulas and SS all together. Would I paste this formula into the Ball-cell?
-
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
-
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?
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives