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
