How to copy the cell values from diffrent workspace sheet into single sheet (Automate the process)

Ravi Sagar

Hi Team, quick help needed.

for example i have 10 project (each is a workspace), each project have 1 sheet with multiple columns and i need to fetch information of 4 columns from this sheet AND then put it on the separate sheet (can call it as master sheet) has information on project level i.e each row is a project in this sheet which would be coming from each workspace sheet.

Note: each workspace/project sheet and the master sheet do have a column like primary/unique identification which can be used.

ONLY THING TO KEEP IN MIND IS WE WANT TO AUTOMATE THE PROCESS AND NOT MANUALLY FEED THE INFORMATION INTO IT. AS THE NUMBER OF ROWS WILL GROW ON MASTER SHEET AS ITS CONNECTED TO SALESFORCE.

Attaching the sample screenshot for better clarity.

1st sheet is from the each project/workspace. last 4 columns data i am trying to copy. have a GPS number as unique which will be available on the matersheet as well

2nd sheet is a mastersheet where i am inteded to move the data into.

@Andrée Starå @Genevieve P any quick help or thought are welcome.

PLEASE HELP !

Andrée Starå

Hi @Ravi Sagar

I hope you're well and safe!

You could set up a copy row workflow to a so-called helper sheet and then reference that with a VLOOKUP or INDEX/MATCH formula.

Make sense?

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

Andrée Starå

Hi @Ravi Sagar

I hope you're well and safe!

You could set up a copy row workflow to a so-called helper sheet and then reference that with a VLOOKUP or INDEX/MATCH formula.

Make sense?

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

Ravi Sagar

@Andrée Starå we need to copy only some columns not all columns from each sheet to the master sheet. Also how it will map to the specific row on the mastesheet.

i am available for a quick call if you have some time to connect.

Regards,

Ravi

ravisagar13@gmail.com

Andrée Starå

@Ravi Sagar

We can my workaround described below.

Make sense?

Would that work?

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.

Ravi Sagar

@Andrée Starå So you saying to introduce another new sheet(helper sheet) to have all the information collected from different workspace/projects into it. Later to use the indexing function on mastersheet to map and fetch the required data correct ?

Andrée Starå

@Ravi Sagar

Yes, correct.

What do you think?

Ravi Sagar

@Andrée Starå i tried your suggestion, but the automation is not working at all. Not able to understand why its not working. Note: the 1st sheet from where i have to copy a row has all the columns coming in from different sheets/ linked in form other sheet. Is this the reason ? on the destination sheet(helper sheet) i have the same columns with same name created as well.

Any points to consider or keep in mind ? i searched for the solution but no luck.

Ravi Sagar

@Andrée Starå i figured out that if we have the cell link in a sheet and we try to do the copy automation. it wont work ans it is in my case well. i have fields in sheet 1 which are cell linked. So you suggest wont work here. Also, the copy feature is creating multiple rows in the helper sheet which would create issues when we do vlookup/indexing to master sheet.