Issues with Automations on Calculated Fields

JCluff

I have a task tracker for our team that I want to auto-archive tasks 5 days after they are complete. I built a checkbox column that compares the completed date to todays date (using a reference rather than TODAY() formula so the formula works when I'm not in the sheet) and if it's been 5 days it checks the box - which in turn should trigger the automation. The problem is, the automation isn't triggering. When I force the automation to run it recognizes the checked boxes and archives the rows accordingly. I'm curious, is there an issue with having the box check when the sheet is not actively open by anyone? The cell history shows that the box is being checked at the right time by the formula, but I'm assuming that since I'm not in the sheet the automation isn't running? Any ideas?

Paul Newcome

If the reference is a cross sheet reference, it cannot be used as a trigger, but it can be used as a condition.

If you set it up to be triggered daily and then set the condition to be when the box is checked, it should work for you.

Paul Newcome

If the reference is a cross sheet reference, it cannot be used as a trigger, but it can be used as a condition.

If you set it up to be triggered daily and then set the condition to be when the box is checked, it should work for you.

JCluff

@Paul Newcome, thanks as always! Didn't realize the cross sheet reference would interfere with the trigger.

Paul Newcome

Happy to help. 👍️

They designed it this way to avoid circular automations that could trigger each other infinitely.