How To Have Risk Column Automatically Update Based On Certain Conditions
Hello,
Can anyone help me write a formula based on the following conditions.
Risk Status = "Green" if the following conditions are met:
- [Delivery Confirmed] is checked
Risk Status = "Gray" if the following conditions are met:
- [Release Date] is Blank
Risk Status = "Red" if any of the following conditions are met below:
- [Expected Delivery Date] is greater than [Required Delivery Date]
- [Quantity Missing] does NOT equal 0 AND IF [Required Delivery Date] is less than Today's Date
Risk Status = "Yellow" if any of the following conditions are met below:
- [Expected Delivery Date] is 15 or more days LESS THAN [Required Delivery Date]
Best Answer
-
Thank you to everyone who helped the formula below is what worked for my application:
=IF(OR([Delivery Confirmed]@row = 1, [Quantity Missing]@row = 0), "Green", IF([Vendor: Expected Delivery Date]@row < [HC: Required Delivery Date]@row - 15, "Yellow", IF(AND([Vendor: Expected Delivery Date]@row > [HC: Required Delivery Date]@row, [Quantity Missing]@row <> 0), "Red", IF(AND([HC: Required Delivery Date]@row < TODAY(), [Quantity Missing]@row <> 0), "Red", "Gray"))))
Answers
-
You will need imbedded IF statements- something like below- maybe this will be close enough for AI to fix…
=If([Delivery Confirmed]="Yes","Green",IF(and([Expected Delivery Date]>[Required Delivery Date],[Quantity Missing]<>0,[Required Delivery Date]<today()),"Red",IF([Expected Delivery Date]>[Required Delivery Date]-15),"Yellow","")
-
Try this, should be a good start:
=IF([Delivery Confirmed]@row = 1, "Green", IF(ISBLANK([Release Date]@row), "Gray", IF([Required Delivery Date]@row - [Expected Delivery Date]@row > 15, "Yellow", IF([Expected Delivery Date]@row > [Required Delivery Date]@row, "Red", IF(AND([Quantity Missing]@row <> 0, [Required Delivery Date]@row < TODAY()), "Red", "")))))
-
Thank you to everyone who helped the formula below is what worked for my application:
=IF(OR([Delivery Confirmed]@row = 1, [Quantity Missing]@row = 0), "Green", IF([Vendor: Expected Delivery Date]@row < [HC: Required Delivery Date]@row - 15, "Yellow", IF(AND([Vendor: Expected Delivery Date]@row > [HC: Required Delivery Date]@row, [Quantity Missing]@row <> 0), "Red", IF(AND([HC: Required Delivery Date]@row < TODAY(), [Quantity Missing]@row <> 0), "Red", "Gray"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!