I have the following columns in Sheet 2:
- A column in which I manually entered numbers (to use in a DISTINCT formula), numbers 1 to 100
- A column with a DISTINCT formula that pulls distinct values from a column in Sheet 1
- A column that flags if that distinct value in column 2 is already in Sheet 3
- 2 or 3 additional columns that use cell-reference formulas to return values from Sheet 1 based on the distinct values in the second column
My goal is that when the column is flagged (whenever the list of distinct values in column 2 changes), an automated workflow will trigger to copy that row to Sheet 3. In the future, when that distinct value appears, it won't be flagged anymore because it already appears in Sheet 3.
1 | OLLI-197 | Flagged | Connor | 145.55
2 | OLLI-192 | Not Flagged | Reynolds | 137.44
The first row would be copied because it is "flagged".
I've learned you can't use columns with cell-reference formulas ("Connor" and "145.55" above), and apparently columns that refer to other columns in the sheet with cell-reference formulas (the flagged column) to trigger the workflow.
I also learned that if I try to bypass something triggering the automation when something happens (e.g., "when column 3 changes to flagged") or to nothing (no when added to trigger), you can't use HOURLY to initiate the automation.
Column 2 (e.g., OLLI-197) will update as distinct values are added in Sheet 1. Column 3 ("Flagged") will update as rows are copied, and the distinct value appears in Sheet 3 over time. Columns 4. 5. etc. will update as Column 2 updates. Column 1 is static, its just manually entered numbers.
How can I trigger the automation to copy the rows then, if every column changes based on either the DISTINCT formula or a cell-reference formula or a referral to a cell-reference column? I'd be happy with it running HOURLY, but I can't set it to do that either.
