Interlinked individual Sheets to Master sheets

Benn · 07/15/21

Hi Experts,

I need help. I have 6 individual sheets that I need to rows to combine inside one Master sheet and update/add rows automatically when the individual sheets gets updates. Is this possible? I know it is possible when we use report builder but I want my Master sheet into Sheet because I am going to make a Metric Sheet to summarize the Master Sheet. Formula can't be referenced to reports.

Please help me. Right now, I have only datamesh as my addon but It has different function.

May I know if there are other addons that can make this process possible? I am open also to learn API but have no reference that I can learn it easily. API document is really hard to understand.

Thank You in advanced,

Julius

Andrée Starå · 07/15/21

Hi @Benn

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.

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!

Benn · 07/16/21

https://community.smartsheet.com/discussion/comment/298126#Comment_298126

Hi Andree, Thank you for your response. My problem on using formulas is I am exceeding the reference sheet limit. I have too many individual sheets (more than 20).

Andrée Starå · 07/16/21

@Benn

Happy to help!

How many columns/rows do you want to reference back to the master from one of the individual sheets?

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)

Benn · 07/18/21

Hi Andree, I have 42 individual sheets, 42 rows and 8 columns that needs to get referenced. I found the solution from FB Smartsheet Unite page and want to share it here.

Make a report and select all 42 individual sheets to combine all of them.
Create a Master sheet as your destination sheet.
Use Data Mesh to join/union Report and Master sheet. Assign Look up columns (Actual Look up column in Master sheet is blank), then set the setting to Update and Add Data.
Run the Data Mesh and it works like magic. It syncs also the changes from the individual sheets to Master sheet.

Interlinked individual Sheets to Master sheets

Answers

Help Article Resources

Categories