Copying rows, but without automatically adding every single column into the 2nd sheet

Josh W

I didn't see a discussion about this with a quick search, so here goes:

When I copy a row from one sheet to another, the 2nd sheet automatically gets updated to include every single column in the original sheet. This is a real hassle when I have 220 columns in the original sheet, but really only want 40 or so of them in the 2nd sheet. I end up hiding 180 columns of unnecessary data.

Then if I ever add a column or change a name in the original sheet, the 2nd sheet gets yet another column added during the next copy event.

a) is there any way to use the copy function to create a new row, but not add Every Single column into the 2nd sheet?

b) it would be fantastic to have the option when setting up the copy automation to choose whether to add all columns, or populate the new row using only the columns that already exist in the 2nd sheet.

Thanks!

Andrée Starå

Hi @Josh W

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Josh W

HI Andree,

I am using Index/Match in a bunch of places as you describe, and I have some helper sheets as well. Those are all good tips, thanks!

But in this case it isn't quite enough. What I'm actually doing is: A product manager (not me) adds a project to the main sheet, which then gets copied by an automation to a helper sheet so that certain 'organization' columns can be added, then it gets copied again to yet another sheet to display several Gantt bars as a project and product Roadmap.

I have contemplated if the primary column of the helper sheet could be set up with a formula to always equal the primary column of may main project sheet, then have lots of other index lookups, and trigger the automation to send it to the final sheet when some particular matched column is not blank. Does that make sense? Maybe I'll try a test sheet.

Genevieve P.

Hey @Josh W

One thing to keep in mind - workflows cannot be triggered by cell-links or formulas looking into another sheet. The trigger could be date-based, or set on a manual change.

Cheers,

Genevieve