Cells Change Dynamically Due to Formulas

I set up an automation to copy a row from one workbook to 1 of 3 others depending on the contents of Column A. The contents of Column A are determined by a series of formulas. The problem is that the contents of column A change dynamically as the formulas process, so the row ends up getting copied to multiple workbooks.

Any thoughts on how to account for this?



One solution you can consider here to ensure automation is triggered only for the first time column A is set is:

  • Define another column B as formula ( Auto-fill all the column B cells )

  • =IF(B1=NULL,A1,B1)

  • Now define automation to trigger on this column B update instead of column A.

  • This way only the first time A1 value is computed it will trigger automation to copy row-1. All following updates on A1 will be ignored.

Note: As this solution leverages self-referential formulas, you will need to enable iterative calculations in workbook. Please refer: How to enable iterative calculations?