Move rows Automatically | Automation Workflows



I have 1 source sheet and 1 target sheet. Using Data Mesh/ Shuttle, I'm adding the records to the target sheet from the source. There are chance that few records in the source can be deleted later and such records are not to be retained in the target as well.

So, I added a column formula in the target that checks if particular record exists in the source and if not it assigns a value, say, "Remove from scope". With this column to be the condition and trigger to be "When rows are added or changed", I created an automation workflow to move such records from the target to Archive sheet.

Since the record is deleted in the source, there won't be any addition/ changes to the record in the target and so automation workflow is not working as expected. Upon research, I found that formula cells can't trigger the workflows too. Looking for the solution/ workaround to move the obsolete record from the target sheet to the archive sheet without any manual intervention