Need a formula to copy column data from 2 source sheets to single target sheet.

Swetha

I have 2 source sheets with similar column names and need to be move the data to target sheet, without using the automation. Source sheet will be having multiple columns, i just need to move data from selected columns.

Source sheet 1:

Source Sheet 2:

In Target sheet, i need selected column values:

Anson Cheung

Why do you rule out the possibility of using automation to delete & copy rows? You can hide Institue and Cost columns in the Target sheet if you don't want to see them. Otherwise, you may need to use the Data Shuttle add-on, which needs extra cost.

Andrée Starå

Hi @Swetha

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.

To connect them row by row, you could 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 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!

Swetha

@Kelly Moore : Is there any possibility, you can provide me Vlookup formula for above?

Kelly Moore

Hey @Swetha

What about a report? If bringing to a dashboard, reports can be used. A report wouldn't take any continued maintenance and would always be up to date. A report would be instead of trying to bring the data to a target sheet - the report would have all the data.

Would that work for you?

Kelly

Swetha

@Kelly Moore : Yes i used Report. It works!!! Thanks!!! Is there any way we can write formula for this.

Kelly Moore

Not easily. If you had names already in your target sheet you would have something to match to, but essentially you want to move data from two different sheets into one target sheet. Without a reference point, smartsheet doesn't know where on the sheet you want them placed. You would have to manually update one, maybe two columns (since you have two sheets) to keep a reference point going. If the report works, it's evergreen and as I tell the colleagues I support, 'it's hands-free'. I am always looking for the hands-free solutions.

If you needed it in a 'sheet' and you have access to the Pivot App, the Pivot app would generate this info and keep it in a sheet for you. Pivot app is a premium app that might have - it depends on your smartsheet subscription.

If you do have Pivot app you would first create a Row report to gather the data from the two sheets. The report would not be grouped or summed, just a plain report. You would use this report as the 'source sheet' for the pivot app. This is also a 'hands-free' solution, once set up.

Kelly

Swetha

@Kelly Moore : Thank you very much for explaining!!!