Copy row when certain criteria are met and edit data within the row

ERD

Hello,

I am managing a list of products where our customers often re-order the same product the following year around the same time. I want to copy the product specifics to the bottom of the sheet once it is checked as delivered and add a year to the due date. This will allow us to plan for the product delivery the following year and reach out to the customer accordingly.

I know I can copy the row once certain criteria are met but can I copy and edit the data automatically?

Thank you

Paul Newcome

You can copy the row to another sheet, use formulas in the second sheet to generate a new date, then copy the row from the second sheet back to the first.

ERD

Ah brilliant, thank you very much!

Paul Newcome

Happy to help. 👍️

ERD

I have tried this and where I am struggling with this populating the new date - Ideally I want the second sheet to copy the row and add 365 days to the due date but I cannot see any functionality to add to a date in the automation. Is there a workaround? I need the due date to update when copied back to the original sheet so that it is assigned to the next year. Am I missing something?

ERD

I figured this out, I added a column in my original sheet called "Carry due date" which was Due date +365. When rows are copied through automation, the formulae do not also copy, so when the rows copied to the helper sheet, "Carry due date" was no longer a formula. In the helper sheet, I made the "Due date" = "Carry due date" and then copied this back into the original sheet. As none of the formulae copied back, the new due date showed with an extra 365 days.