Copy Specific Columns From One Sheet to Another, Linked

lola.brooks113611

Samuel Mueller

Nov 29, 2023

@lola.brooks113611 You could use a helper column that makes the unique identifier blank if it does not match the criteria, and then use that column in your DataMesh workflow. You could send data immediately but only if it meets the criteria. This would get around the hourly limit but allow you to use a criteria like you would in a report.

Sorry, I'm not following the process. Can you give me an example of how this works?

Samuel Mueller

@lola.brooks113611 lets say that Row ID is your unique Lookup Value between sheets. For the source sheet, you create a helper column called DataMesh Helper, and you put a formula that returns the Row ID only if a certain criteria is met, such as Status is Active. Then, only rows that match the criteria would be added to your target sheet. This process would allow you to update your target sheet immediately, based on a criteria. So you see in my second image the lookup value would DataMesh Helper, not Row ID for the Source sheet.

It does still take a minute or so to update the target sheet, but much quicker than an hour.

Source Sheet

DataMesh Step 3

DataMesh Step 4

lola.brooks113611

Thanks for the tip. I was working on something more complicated than I explained, but I was able to use a combination of actions that included your suggestion to solve my issue. I fixed the need for a formula by using datamesh to move the cell info to a second sheet and then used datamesh again to move it back to a helper column so I could use that action to archive the row of data. Thanks again!

Steven Munsey

This would be a great optimization! Our use case involves copying a risk in our project risks worksheet to the project issues worksheet once a risk becomes an issue. We collect different data elements for risks (example: risk score, risk response, etc.) that we do not collect on the issues worksheet. Being able to select which columns we want to copy from the risk sheet to the issues sheet would prevent the addition of non-useful columns in the issues sheet.

enzo_vaire

Adding my support for this idea - I will try helper sheets etc., but this feature would be a significant improvement for automations. In the ideal world one would have a mapping from column to column with the option of functions (for instance, Column A in target sheet is Column B + Column C from the source sheet), but even simply mapping would help!

TCJ

Hello, There is a 3rd party, Smartsheet Guru who claims to have exactly the features that would be helpful. The ability to combine a report (select specific column, apply filters etc) AND bring that data into a sheet where you can do anything you need to it. Smartsheet Report to Sheet - Smartsheet Guru

. So apparently, this is possible, just not available from Smartsheet.

Danielle Wilson

Hi @TCJ This is available as part of Smartsheet DataMesh!

Amanda Fulbright

We need this as well. Our use case is managing a multi-level governance process. We have market-level and national governance. I want to copy approved rows from market-level governance sheets to a national sheet. We need to retain the attachments and comments, but would like to select specific columns leaving behind non-relevant market details.

MTS

For the following comment, can you provide an example of what this formula would look like?

Here's a workaround that I think hasn't been mentioned.

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.

Andrée Starå

MTS

Aug 15, 2024

For the following comment, can you provide an example of what this formula would look like?

Here's a workaround that I think hasn't been mentioned.

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.

Hi,

I hope you're well and safe!

Here you go!

=VLOOKUP(CellThatHaveTheValueToMatch@row,{RangeThatHaveTheValueToMatch*}, ColumnNumberWithTheValueYouWantToShow, 0)

*Where the first column is the ColumnWithTheValueToMatchAgainsTheCell

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0)

I hope that helps!

Be safe, and have a fantastic day!

Best,

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

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Tama Wagner

It would be a GAME CHANGER if in the "Copy to Another Sheet" or in the "Copy Rows" automations/workflows, that we could choose which columns to copy instead of being limited to sending over every column included in the row.

As an example, I have an automation to copy a row to another sheet when a box is checked. The sheet I'm copying from is a master list, so it has A LOT of columns. The sheet I'm copying to only has maybe 5 columns, and I would really prefer that all the columns from the master list not be added to the other sheet.

As a bandaid, I hide the columns that are unnecessary to the sheet, but when it comes to situations where I'm building a report, having so many unused columns makes the process more complicated.

Cindy B

We truly need the ability to copy specific rows to a new sheet, even if we have to map the fields. It is impossible to copy full rows when you have a 'system' field such as created/modified. Utilizing 3 sheets to handle 1 task is inefficient, uses too much space, and creates unnecessary sheets.

Danielle Wilson

@Cindy B if you are open to mapping fields, DataMesh will allow you to do just that and map and copy over new records with only certain columns.

JenO

This is another feature I would really find helpful and another one that has not been updated in years. For me I have a sheet with all the data that comes via a form and then various admin manual updates are made and pricing for jobs is done up. I don't want the sensitive information in this sheet to be linked to the production floor data and therefore having the ability to turn certain columns into the production order on a separate sheet would solve my issues.