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

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

Sign in to vote!

Find more posts tagged with

Idea Submitted

DataMesh

Cell linking

Enhancement Request

Comments

Genevieve P.

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

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.

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

Quick Links

Trending ideas

Report Widget Capability - Drag & Drop to create Report Widget in Dashboard from Workspace Panel
Hi, I was creating new dashboard and thought it'll be so nice to have easy drag and drop Report widget capability from Workspace Panel since I always group set of reports under one folder (for ease of finding them what goes on the dashboard). I think this would be great time saver especially if you have similar name…
Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
Asset Sharing should default to View, not Edit
Since the license model has changed, the option to Share should default to VIEW, not EDIT. The View option allows less-experienced users to share that way by default, and share as Edit only when needed. This allows for less cleanup during true-up. This also prevents users from adding provisional licenses unknowingly.