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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!