Automate Copy of One Cell Only - not the row

Iain

Hi all,

I'm trying to automate an element of work that I currently do manually. I have two sheets, Project Intake and Pipeline. When a new entry is added to the Project Intake sheet I want it to automatically copy the Project ID cell only to the Pipeline sheet. I then have a datamesh set up to run and copy over only the data I want for that project into the Pipeline sheet.

Can anyone tell me how to do this, or provide an alternative way to automate this please?

Cheers

Andrée Starå

Hi @Iain

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.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the 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!

Iain

Hi @Andrée Starå ,

Would those options work for when a new row is added on the source sheet? i.e. a new project is added to the sheet so a new Project ID is automatically created. I then want that new Project ID to be automatically copied across to the destination sheet without any manual intervention. I've not got a lot of experience with VLOOKUP or INDEX/MATCH formulas but don't they need to know what they're looking for in order to work? So I would still have to manually update the destination sheet with formulas to pick up new entries on the source file?

In this case I just want the destination sheet (Pipeline) updated when a new Project ID is created in the source sheet (Project Intake). Once that Project ID is in the destination sheet the datamesh kicks in to collect the rest of the data in the destination sheet.

Andrée Starå

@Iain

Yes, it will work if you use the Autonumber method.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Make sense?

✅Remember! 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!

Iain

@Andrée Starå

Yes makes sense, thank you. My concern is the "manually add the row id on as many rows as you think you need". This implies that this will eventually break requiring manual intervention.

It's a shame that the automation functionality only allows the copy of the entire row rather than select cells. I guess I could just copy the entire row of data and hide the columns I don't want...

Thanks for your help.

Andrée Starå

@Iain

You're more than welcome!

Yes, you could hide the column your use my other described method, but if it doesn't matter, I'd recommend hiding them.

✅Remember! 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!