Select which columns to copy or move from one sheet to another

rlacout

There is no clean way to populate a target sheet using automation/workflow upon a certain condition. I think the ability to use automation to copy only certain fields from a row to another sheet upon a condition would be a great addition. Currently, you can copy a row, but I think being to copy desired fields would be very helpful.

Here is one question I found on the community board, but the solution is not clean.

https://community.smartsheet.com/discussion/69306/can-i-use-automation-to-copy-only-certain-fields-from-a-row-to-another-sheet

Please feel free to contact me at rlacouture@asc-es.com.

Thank you,

Rick

TKh

is there a simple way to copy columns from one sheet to another similar to excel without such complicated instructions? Does Smartsheet has a simple way of doing simple tasks?

Mary Anne

Adding another users request for this feature, we need the ability to copy select columns from one sheet to another.

Unless I'm missing something, the data mesh, vlookup or index/match won't work if you don't have data in the new helper sheet to pull from.

The concept here is that I want to share data from a source sheet into another sheet for people to utilize, I have nothing to match and nothing to mesh.

I guess one option would be to download your source sheet, load it into data shuttle and then you have the ability to manage which columns populate a new sheet. The issue is that there is no automation to run an export of data so exporting the data then saving it to a sheet or sharepoint to shuttle is all manual.

Danielle Wilson

@Mary Anne If you do have DataMesh on your account, that's what I'd recommend for that! If you use the "Copy and Add" mode in DataMesh it can essentially do an INDEX/MATCH that pulls in new unique IDs as well, so you don't already need to have the value in the sheet to look up against. It's essentially the Data Shuttle tool for when you want to bring data from one sheet (or report) to another sheet (instead of from a CSV/XLSX file to a sheet).

Mary Anne

@Danielle Wilson Can you send me more information on how this is done? When I look at DataMesh it wants me to have a lookup value in both sheets to tie the data together.

What I am trying to do is copy 5 columns worth of data from my source and add it to my target where the target starts with nothing in it. When a new row is added to my source, I would like all 5 columns of that row's data added to my target.

I created a new DataMesh and gave this a try, but nothing copied over when the mesh was run. It successfully ran but didn't pick up any records. I'm guessing that's because my target doesn't have a value in the lookups value field.

If you can send over the instructions on how you do a copy from a source and add it to the target as new rows / columns with no target sheet value in the lookup value field that would be great.

Danielle Wilson

Hi @Mary Anne - you will still need to have a Lookup Column selected in both the source and target but you don't need to have those 'keys' already populated into the target sheet, if that makes sense. For example, in this config, I have Lookup Columns selected (which I need to have) and the columns that I want to map over. BUT, when I run this, even if my "Time Submission" target sheet is totally blank before I run the DataMesh config, upon running the config, the target sheet will populate with all of the values for those three columns (if I didn't want all of the Email Addresses to come over, I could first make a report to filter out the values I didn't care about bringing over). It works in this way specifically because I have "Copy and Add Data" selected as the Data Mapping Format.

If you do have DataMesh access, I'd recommend reaching out to your CSM for some help getting started or booking a Pro Desk session if you have Pro Support.

Mary Anne

@Danielle Wilson THANK YOU so much for this info, totally worked as I did forget to change the mapping format to copy and ADD data. This is fantastic!! Exactly what I need, now I can delete my data shuttle!! :-)

Melvin Collins

Copy only cells in a row with value to another sheet. Smartsheet currently only can copy the entire row which can give you a lot of empty space from a form you may not want. I.E., If there are 25 columns or form questions and I only want to copy cells from that row that have data in them and the cells with data may change on each form.

When an end user fills out a form and ask for a copy smartsheet sends an email with only the data the end user entered. I need this exact result

LAM

I need to be able to copy a row, cell or group of cells and paste them into another sheet. I often reference prior project schedules for lines needed on a new schedule. Being able to copy the item (rather than clicking into the cell and copying text) would make this much more efficient. Thanks or your consideration!

Ken Richards

I would like to suggest this also. I have a use case where I would like to create a change log for changes made to a source sheet. When a change is made to specific columns in a source sheet, it copies a subset of the fields in the changed row into the source sheet into a "Change Log" sheet where I can run reports and metrics.

Helper sheet ideas using VLOOKUP/INDEX/Data Mesh won't work. Normal copy brings over all fields 40+ when I only need about 8-10 of them. A waste of space and storage.

A suggestion/approach would be to do the normal copy but only copy over the fields from the Source sheet that match fields in the Target Sheet. Any fields that aren't in the target sheet aren't copied. If there are fields in the Target sheet that don't get populated, they can be left blank and filled in later, or if they have formulas they can calculate after the rows are copied.

It may also be possible to select specific fields from the source sheet and copy them to specific fields in the target sheet. This would be a workable solution, but the one above might be quicker to implement and to manage.

Thank you!

ThermoKate

Allow users to select which columns are to be copied or moved to another sheet. Unnecessary columns would no longer have to be hidden.

sarshad

You can also look at using Power Automate or preferably Zapier. You can pull in each row from source sheet based on a trigger (for ex: when new rows are created) and then create a new row with specific columns in the destination. Another way will be to use Data Shuttle: you can setup an offload workflow on the Source sheet for columns you need to move/copy and then an upload on the destination and map the columns.

dylanchaney

WORKAROUND:

Why not use INDEX() and reference another sheet? For example, I have a database (let’s call it Construction1) that logs fields submitted from a form for Construction Impact Messages (CIMs). I also have a Metrics Dashboard to plot those quantities by month on a line graph.

In order to keep the line graph automated and updated, I have copied the formula below into 500 rows in my Date column (I’ll have to copy and paste again if I ever get past 500):

=IFERROR(INDEX({Construction1 Range 1}, [DateHelper]@row), “”)

This pulls the row number I want to reference from my DateHelper column so I can quickly and easily copy and paste my formula. This also keeps my column updated when any new rows populate in my Construction1 database.

Note: my DateHelper column is a simple additive formula, where [DateHelper]1 = 1, [DateHelper]2 = [DateHelper]1 + 1… etc so that column is easy to copy/paste as well

See below for my table and metrics:

HB_admin

Same thing your copy sheet or copy row automation does, except I'd like to have to capability to isolate the data to be copied to a certain column of info on a given row.

For example, on a sheet where I track potential and active projects, if I have a column with project status, I'd like to copy a few pieces of info within that line to another sheet if I change the project status column from "business development" to "active."

Danielle Wilson

@HB_admin I would recommend DataMesh for that! You could create a report from your sheet of active projects so it only shows projects with a status of active and then use that report as the source for a DataMesh workflow and target your other sheet. You can choose to map just a few columns of information.