Transfer Info Between Sheets

CaraBart28

Hello,

I am trying to transfer information from one sheet to another. The copy to new sheet wont work as the columns are not exactly the same and we do not need all of the information from the original sheet moved. I am also trying to figure out how to get it to automatically copy over to the new sheet when new information is entered into the original sheet that meets specified criteria.

Thank you!

Andrée Starå

Hi @CaraBart28

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 week!

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!

CaraBart28

Thank you @Andrée Starå I am not the best with Vlookup or Index matching, so I think this is why I am having difficulty doing this. Here are the two sheets that I am referencing, here we are trying to get the highlighted columns in the original sheet to copy over to the destination sheet, however, we are only interested in those associated with St. Joesph's listed under the QCN name.

Genevieve P.

Hi @CaraBart28

I've removed your second image as it looked like it contained personal information (emails) and this is a public forum.

Based on your description, it sounds like you don't have a matching unique value in both sheets (e.g. a name) but instead need those unique values to be added across sheets, along with some pertinent information, is that correct?

If so, do you know if your company has access to Data Mesh? Data Mesh would be an ideal add-on for this scenario. It can add new rows as they come in to your source sheet (and you can set up a Report as the source so it filters for only the rows that match your criteria), then map columns across sheets to pull in new data as it's added.

Here's more information:

https://help.smartsheet.com/learning-track/integrations-and-add-ons/datamesh

Cheers,

Genevieve

CaraBart28

@Genevieve P. I created a report as you stated in order to filter the data to one location. However when trying to setup the config, the data source can only be a sheet, not a report. so how do I go about this?

Genevieve P.

Hi @CaraBart28

As long as the Report is a Row Report it should still show up in the first window of the Data Mesh configuration to select. Reports will show up with an Orange icon:

You could also search for it by Name.

Let me know if you're seeing something different in your config window!

Cheers,

Genevieve

CaraBart28

Thank you, for some reason it was no populating, but it is now. So when I run it, the transfer sheet I have is blank, and should be pulling over about 30 rows of data from the source sheet, however, it only pulls over one row, what am i doing wrong??

Genevieve P.

Hi @CaraBart28

Can you post screen captures of how you've set up the workflow? The 3rd and 4th tab of the workflow configuration will help us determine why only some rows may be pulling through.

I would also recommend going through the DataMesh eLearning course in the Smartsheet University.

CaraBart28

Tab4.jpg

Tab3.jpg

Hi @Genevieve P.

I have attached tabs 3 and 4. Thank you for the link for DataMesh in SmartSheet University!

Genevieve P.

Hi @CaraBart28

I see that you have the "lookup values" set to be the "Sheet Name" on the Report and the "Affiliated Practice" in the destination sheet.

If you only have 1 sheet in this report, then DataMesh will only bring over 1 row because the rest of the rows have a duplicate value (the same sheet name, over and over again).

Is the NPI unique per-row? If so, that's the column I would use as the Lookup Values.

You have the restrictions set so if the NPI exists already in the second sheet it won't be overwritten with new data, but if the NPI doesn't exist, that content will be added to the sheet.

Let me know if that helps!

Cheers,

Genevieve