Tick a checkbox depending on value of another field
I have a multi column Smartsheet that tracks items by date. Part of the functionality shows the number of days the item has been active:-
=IF(OR([S.Status]5 = 3, [S.Status]5 = 1), TODAY(0) - [Start Date]5)
There is also a check box column. What I would like to do is to automatically tick the check box if the number of days is more than a pre-determined value (let's say 20). This is simple enough:-
=IF([Days in Queue]5 > 20, 1, 0)
Now for the "but". Each item within the Smartsheet can move to another team and when this happens the [Days in Queue] goes back to zero, however what we need to happen is for the check box to remain ticked if it has ever been ticked.
Example:
[Days in Queue] is 15 and item moved to another team and then back to this team (checkbox not ticked and [Days in Queue] value returned to zero)
[Days in Queue] gets to 22 - we want the check box ticked when the value was 21.
Item moved back to another team and then back to this team (checkbox must remain ticked and [Days in Queue] value returned to zero)
[Days in Queue] gets to 15 and then the item is set to closed at which point the [Days in Queue] is made null but we want the check box still ticked because at one point in its life cycle, the item's [Days in Queue] went over 20.
All assistance greatly appreciated.
Thanks,
Matt
Comments
-
You can add in another date type column. One that records the ORIGINAL date that does not get updated as it changes teams. Then use that date in your above process.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!