Linking Sheets together in a compiled/ master sheet with real time updates

Rana M · 08/01/24

Hello,

I'd like to propose an idea to help innovate smartsheets already spectacular features even more.

One feature that I do not see is essentially combining / stacking sheets as a compiled master sheet. The compiled sheet should reflect updates made from each individual sheet.

There does exist an automation where one can run the automation and the existing data will copy to another sheet; but this tends to copy the individual sheet's data with every rule ran.

The next option is to link each row from one sheet to another; but linking multiple lines of multiple sheets individually can become an ever increasing task that would not be time efficient in any workspace.

It's very similar to the concept of combining a folder of different excel sheets in using power query. Essentially, all the workbooks / sheets that are individually updated, will be reflected in the combined sheet.

This youtuber highlights the concept I'm talking about quite nicely, and I've timestamped if interested in viewing: https://youtu.be/_ede9e5qDIM?t=419

Case Example:

I have multiple sheets of customer feedback data that is divided per month for each sheet. All of the fields are the same in each of those sheets.
To get a comprehensive view of all months per each sheet, I'd like to combine that data as one single sheet so I can get a full report and aggregate data more readily. This would also allow me to connect to my visualization software more readily.
Any changes in any potential "Notes" field will also be reflected in the notes column. This will allow any updates from the individual sheet to be reflected in the compiled sheet.

Thank you,

Rana

Genevieve P. · 08/02/24

Hi @Rana M

In your specific case, what about using a Report? A Report can do exactly what your diagram represents: bring together multiple sheets in one place. Ensure your column names are the same across your sheets, then all your data can appear in one Report together.

I like to think of Reports as open windows into the underlying sheets. This is because it's not housing the content itself, but it's allowing you a window into a row that exists somewhere else, meaning that if you make an update to a cell in a Report, that update will happen on the Sheet! And if the Sheet is being updated, the Report will show that automatically. It also means that your Conversations panel will show all the comments from the underlying sheets as well.

Here's more information: https://help.smartsheet.com/learning-track/onboarding-webinars/smartstart-reporting

Cheers,
Genevieve

Rana M · 08/04/24

https://community.smartsheet.com/discussion/comment/447578#Comment_447578

Hi @Genevieve P. Thanks for the quick feedback and insight!

Having the compiled sheet/report would give a nice breakdown, but my follow up question would be if it provides users a way to utilize that compiled data further?

Using my original example to provide more clarity- If I were to combine multiple sheets into one compiled master report, would I be able to reference that report data in another sheet if I need to pull data for any reason?

For example, If I make a new sheet that only looks at a few customers from specific months to fulfill some type of new business task, and I need to run a function such as =INDEX(MATCH in my new sheet, will I be able to reference that compiled report in any capacity? This may be my misunderstanding, but I don't believe I can reference report data in that capacity?

But if my assumption is correct that I wouldn't be able to reference report data in a new sheet; then having a way to reference a continuously updated compiled sheet (made up of individual sheets), instead of a report, would essentially provide a compiled repository of individual sheets that can be referenced for further business tasks. But again, this could be my lack of knowledge so please do let me know otherwise!

Thank you again!

Genevieve P. · 08/06/24

Hi @Rana M

You're correct that a Report can't be referenced by Formulas in other sheet (you can add your vote to this request here: Reference Reports in Formulas (e.g. VLOOKUP) to sheets)

That said, on a Business and Enterprise plan you can use the Grouping and Summary features for quick summaries in your Report (see: Redesigned Reports with Grouping and Summary Functions)

And in your scenario, what I would suggest doing is creating a new Report (instead of Sheet) with your same criteria, looking across all sheets (filtered by Customer, Month, etc).

Cheers,
Genevieve

Sign in to submit new ideas and vote

Linking Sheets together in a compiled/ master sheet with real time updates

Idea Submitted · Last Updated 08/01/24

Comments

Categories