Automatically delete information in copied cell from workflow.

S.OvadyaRdesign

Hello everyone, hope everyone is doing great.

In a case we have an automatic workflow that copies a row automatically to another sheet (destination) when new rows are added or cells are changed in the mother (source) sheet. 

How to make an automated workflow for when we delete or change information in a row inside the source sheet (row is being copied to the destination sheet through automatic workflow) it deletes the information also automatically in the destination sheet (where the row was copied)?

Lets say that we have our mother sheet that contains the tasks for everyone in our team and we have multiple personal sheets for each person. The personal sheets have their data auto-populated by an automated workflow that basically when the name of the team member appears in the source sheet in a column that shows who is assigned to a specific task it automatically sends it to the destination sheet, so how can we do that when someone is not assigned to an specific job anymore (we delete his name in the "assigned to" column) the row that was copied to his sheet (destination sheet) is automatically removed or deleted?

If there is no workflow or automatic way of doing so, is there a workaround we can use?

Anson Cheung

Have you looked into Row Report? It can always show real-time updates from the source sheets.

Julie Fortney

Hi S.OvadyaRdesign, I agree with @Anson Cheung - Unless you have concerns about sharing your source sheet with the team, I highly recommend using a row report instead of copying rows to other sheets.

As long as it's ok for your team to have view access to the source sheet, you can create a report from it and filter the assigned to column for the current user. That way you only have to create one report, and everyone will only see their own tasks.

If you have concerns about giving your team view access to the whole source sheet, you could consider Dynamic View, which is an awesome premium app.

Back to your original idea of deleting information in copies of rows from a source sheet - if you really need to do this, there's a complicated workaround:

Here's my source sheet:

Here's my individual task sheet. I have a workflow on the source sheet to copy any rows assigned to me to this task sheet:

I added two columns to help us identify when the assigned to changed on the source sheet, and trigger another workflow to move it to an archive sheet. (we can't just delete whole rows)

Here are the formulas used:

Current Assigned To: =INDEX({test - Rdesign source - Assigned To}, MATCH(Task@row, {test - Rdesign source - Task}, 0))

Assigned To Changed, Move to Archive: =IF([Assigned To]@row <> [Current Assigned To]@row, 1)

Note this is clunky for many reasons - if you have a new team member, you have to set up a new task sheet for them, and a new workflow to send copies of rows to their sheet. Also, since the Current Assigned To formula is finding a match for the "Task," if someone makes an edit to the Task name on the source sheet, your task sheet won't be able to match it.

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

S.OvadyaRdesign

Thank you @Julie Fortney, I am trying it right now.