Is there a way to duplicate a line with slight modifications to one cell?

PANWordslinger

I'm trying to find a way to duplicate a line based on data from another cell. For example, if I have a line that includes A, B, C, and/or D in a list in one cell, I'd like to make duplicate lines where the Primary column name is changed to {Primary column} - A/B/C/D, while all other data from other columns is preserved.

So, there could be one to four duplicate lines needed depending on what is selected in that list.

I've tried building automations to do it by copying the line to a swap sheet, changing it, then copying back, but that only works once and then the automation in the Swap Sheet breaks and needs to be re-enabled (and even then I can't get the Primary column data to be retained properly, it literally changes to "{Primary Column} - A").

If there's a way to do it based on a form submission, that would be even better, but I haven't been able to figure that out either.

TVang

Yes, it is possible. It's a bit complex but it's possible without third-party tools. The approach outlined below leverages workflow automation, cross-sheet reference formulas, and two sheets that contain the same columns.

I. You need a staging sheet and a final sheet. (Let's call these StagingSheet and FinalSheet.) Your form will submit entries into the StagingSheet.

II. The formulas and automations that will evaluate and create the duplicate rows are all located in the StagingSheet. For example, the formula that creates the value in your "Primary Column" should only exist in your StagingSheet.

III. You'll need to know how many copies are needed in the final sheet.

IV. You'll need a unique ID for each new entry submitted . (This is necessary to help determine number V below.)

V. You'll need to know how many copies of the recent entry exist in the FinalSheet.

VI. You'll need to run the automation for each time that a copy is needed in the FinalSheet. That is to say, the automation that creates the copy will need to run until the number of copies needed matches the count in the FinalSheet.

Part of the challenge is how to trigger the automation in number VI above. Your choices are either manually or hourly (and variations thereof). An automation will not trigger another automation. One exception, however, is when the last action of an automation generates a document and attaches it to a row ("When rows are changed...When An attachment is added").