How to map data from cells from Smartsheet source to Smartsheet target?

Arvind Manoharan

I have a task where I'm collecting information from automation events to a Smartsheet (source). The source Smartsheet contains columns of data of interest. Based on a status in Smartsheet, I want to trigger the copying of data from specific cells in source and paste it into specific cells in a target Smartsheet. The idea is to map data between two cells (one from each Smartsheet).

I am familiar with cross-sheet functionality like vLookup but wondering if my actions to pull data into my target Smartsheet can be based off a daily schedule dictated by a status column in my source Smartsheet?

Do note, the number of columns in my source Smartsheet will be different and labeled differently than those found in my target Smartsheet.

Any ideas on how to setup this mapping? I don't think our company has access to the DataMesh paid functionality in Smartsheet - so looking for creative alternatives from the community.

Andrée Starå

Hi @Arvind Manoharan

I hope you're well and safe!

Have you looked at using cell-linking?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Arvind Manoharan

Hi @Andrée Starå - let me see if i can break it down - cannot share actual sheet as it contains PHI info...but here's what I'm trying to achieve:

Source Smartsheet Columns: Class, Type, Color, Status

Target Smartsheet Columns: Class, Weight, Type, Consistency, Color, Dollar Value, etc.

Based on particular Status from source Smartsheet, I need to copy the cell values for each column that correctly matches/maps to the column cell data onto target Smartsheet. Columns are not in order in the Target and cannot expect it to match the order and number of columns in source.

Note: when status is updated in source sheet, i need the entire row copied over to the target sheet wherein each cell from source maps to the appropriate column in the target sheet. The goal is to copy data from source to target but because the column headers may not be same nor the quantity of columns may not be equal, I need to ensure that the row being copied into target sheet only brings in those that match on a cell to cell basis (based on column name) - essentially map one cell to another based on column name.

So Source "Class" will map to Target "Class", etc. The challenge is that there are more columns in Target Smartsheet than in Source and so moving an entire row of data from source will not work since columns that don't exist in Target will get unwanted columns included.

Therefore, i need to map only the cell values from source to target. Note: I actually have 2 target sheets that the source will feed into and target 1 & target 2 are different from each other as well.

Thoughts?

Andrée Starå

@Arvind Manoharan

Another option would be to use so-called helper sheets.

In short, copy the row to a helper sheet and then use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure 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?

Arvind Manoharan

Andree,

The moving of rows or copying of rows won't work because these functions assume that data columns between source and target are organized equally. The challenge here is that I have a source Smartsheet with far less columns than the target Smartsheet. I need values from cells of source Smartsheet mapped into the correct cells in target Smartsheet. A simple move/copy will not work here. Additionally, since i have two target Smartsheets that could potentially receive data from the source, i need a mapping logic in place such that based on some status on source Smartsheet, the cells will copy over to target using the mapping references in the desired target Smartsheet.

It sounds like if I don't have the DataMesh feature in Smartsheet, this process is difficult and i will need to integrate with external workflow (e.g. PowerAutomate) to achieve this mapping exercise. I want to stay in Smartsheets rather than leverage another tool to achieve this. Thoughts?

Andrée Starå

@Arvind Manoharan

Yes, I think it would work because, with the helper sheet(s), it won't matter what columns you have. You'd only collect the ones you need to the destination.

Make sense?

Arvind Manoharan

Okay - here's what I'm trying to do...I'm focusing one column but eventually, i'll need to address multiple columns between the Smartsheets.

Source Smartsheet - let's focus on the "PROJ #" column

Condition: when Target Location = "Push to General" - i want the target Smartsheet to pull in the cell value of "PROJ #" from source into its corresponding cell.

I want this done for every row that has the status in the Source Smartsheet. A simple IF statement is not helping with the link because it only targets one cell. How do i ensure that every record on my source Smartsheet is pulled into my target based on the target location column in the source Smartsheet?

Target Smartsheet:

william.johnson

@Arvind Manoharan , did you ever find a solution to this? I have a similar situation and am looking for a solution.

Andrée Starå

Hi @william.johnson

I hope you're well and safe!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!