How do I trigger an automation on a sheet that is not open?
The problem: The "on change" automation does not trigger despite the flag changing - this is happening on a sheet that is not running.
The situation: I have 2 sheets.
Sheet 1, The Active List, has a list of active employees.
Sheet 2, The Remove List, is populated via data uploader (replace). It contains employees who leave the company.
When data uploader runs, Sheet 2 is populated with terminated employees. Over on the Active list, I have an Index/match formula in a checkbox field that looks to see if the Active employee appears on Sheet 2. I'm finding that after I upload, the flag will checkbox will have changed to "checked" but the automation associated with it did not fire.
If flag Changes to Checked, then record the date and move the employee.
The flag is changing but the automation does not trigger.
Why does my automation not trigger on Sheet 1 when the conditions are met due to changes based on a separate sheet? How do I fix this?