Copy Row using Automantion via a Trigger cause TWO rows to be created

Teri Ann

SHEET A:

Trigger: When rows are added or changed AND when "assigned to" changes to any value

Action: copy a row SHEET B

EXAMPLE/ISSUE:

Sheet A Row 1 is "assigned to" Joe

This row is then copied to the Sheet B

YEA! however

Joe quits

Sheet A Row 1 "assigned to" is changed to Sally

A second row is then copied (via trigger automation) to Sheet B

OH NO! Now there are two rows on Sheet B, one for Joe and one for Sally

QUESTION: Is there a way to, when the "assigned to" field is changed on Sheet A Row 1, when the trigger is set it over rides that field/row on the Sheet B so there is only 1 row with that assignment (Sally).... not 2 (Joe and Sally)?

SJ Sellers

I don't think this is possible.

I would create a helper column in Sheet B that checks for rows that meet your criteria for the rows that should have been overwritten, then create automation on Sheet B to MOVE those rows to Sheet C - effectively deleting them.

I can think of a couple of different ways to define the criteria for your helper column, but the best method will depend on your data. Probably the simplest way is with a system generated ID field in Sheet A. Then when you have two rows with the same ID in Sheet B, you would flag the one with the earlier created date, and then move those rows.