summary of sheet summaries

My project contains multiple sheets that need to be summarize. Each sheet has a sheet summary but since my data spans across multiple sheets, I need to combine the calculated fields in my sheet summaries to be combined (sum, average, count etc). I realize I can create a metric sheet that makes use of formulas and reference to each sheet but since I have already set up sheet summaries, it would be a lot easier if I can just combine the sheet summaries (i.e. summary of sheet summaries). As you all probably know referencing multiple sheets with conditional formulas can be a pain if you have a lot of sheets and different conditions you are querying. I realize I can create a report that pull information across multiple sheet summaries but reports do not allow calculation. Any workaround to this (other than create a metric sheet with formulas)? I hope Smartsheet has a plan to offer this capability in the near future.

Find more posts tagged with

Sheets

Project Management

Enhancement Request

Accepted answers

All comments

Andrée Starå

Hi,

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Jon Stikka

looking to do the exact same thing... need to summarize data from multiple sheet summaries, and would like to input that into a chart for a dashboard to track progress across multiple projects. Now realizing same issue as yours - can't sum this info on a report, can't cell link the report into a metric sheet, and very labour intensive to write all the fx for this manually.

Darren Pochin

I'm also looking to summarise data from multiple sheet summaries and then include this as a metric on a dashboard. I do not want to create complex formulas on a helper sheet sheet.

This would be a very useful feature for us.

Anthony Barthelemy

I need to do the same

Anthony Barthelemy

Has anyone submitted a product Enhancement to Smartsheet?

Andrée Starå

Hi everyone,

Please submit an Enhancement Request when you have a moment to have your vote added

A New Way to Submit Your Feature Requests

To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Shinya

I can't believe Smartsheet does not have this capability right now. It is so half-baked!

Mike L.

I work with hundreds of sheets that have the same column names. Each row is a different transaction. I use reports to combine sheets or sheets in workspaces. Then I connect excel to the smartsheet report using the data connector (enterprise account or greater required). I use the connections as the source data for a pivot table. I use pivot tables to sum, average, count, etc. If you have access to other tools (tableau, qlik, power BI) you can use them too.

I think the Pivot App might work too and is an in-app solution. I suspect it is not as robust as other tools mentioned above, at least not yet.

If you end up using Smartsheet with multiple integrations I would try using MS-Teams to keep things organized.

Hope this helps,

Paul Newcome

I use a rollup sheet for this. I create a series of columns (one for each Sheet Summary field and then hide them). Then in each of those columns I use a basic

=[First Summary Field Name]#

=[Second Summary Field Name]#

=[Third Summary Field Name]#

etc etc

Then in my rollup sheet I have the same layout. A column for each field. From there you can highlight the cells in a row across all of the columns and cell link them all at the same time to the hidden columns on a sheet.

Now I have live data coming from every one of my sheets flowing into this one.

Does it take a while to initially set up if you already have a lot of sheets built? Yeah.

But once it is set up, it doesn't take but a minute to add a new sheet to the rollup.

If you need to gather additional metrics on all of your sheets other than having just the project data pulled together, now your formulas only have to reference one sheet instead of many.

My own formulas reference two sheets though. Each row has 28 cell links in it. Not only does this slow things down, but it adds up quick towards your limit of inbound links.

So I have an archive sheet set up to receive rows (projects) that are closed out and will no longer be updated via an Automated Move Row.

Keeps things clean and current and allows me to not only track live data but also historical data.

M. David

Paul,

I have tried to understand your approach above to no avail. I think this will accomplish my problem, but am getting lost in your text. Let me explain what I want to do, along with how my files are structured. If what you wrote is a solution, then I will just need a little clearer explanation. I have project files that I've created in CC. In each one of those, I have some summary fields which compare specific dates in the sheet and tell me the number of months between the two dates. I have created a report and graph to get this data onto a dashboard. There are four numbers from each project that I graphed. I would now like to know what the average is of the numbers, and be able to get these averages onto the dashboard. Does your solution accomplish this?

For the solution you described, did you create those columns in the roll-up sheet or in each individual projects files where the Summary Fields are? I tried in the roll-up sheet, but it can just pull from the summary fields in it's own sheet. If you added them to the individual project sheets, then do you just create a regular report to pull the data together?

Like I said, there's something going on and I just couldn't follow exactly your steps for this solution. All help is appreciated.

Thanks.

Paul Newcome

@M. David I created the columns in BOTH sheets and used cell linking to pull them from each individual sheet into the rollup sheet.

M. David

@Paul Newcome That makes sense. It's unfortunate that it has to happen this way, as it completely destroys the benefits of the Sheet Summaries. An issue I just ran into today also destroyed it. I have a sheet for each project, with a pm for each project. Some of them deleted rows on their sheet because their project didn't need those steps. After they did this, I copied my sheet summary fields and formulas, but they no longer work correctly since there are "missing" rows. I wish the software was a little more intuitive and recognized that I don't care about a specific cell's data, I care about what that data represents. If a row is deleted, I want it to move the new formulas up to accommodate. It seems like your solution of adding new columns with just one piece of information at the very top, is a work around for this. Then when rows are deleted, the formulas at the very top would update automatically.

Thanks again.

Dan Tanner

Hey all,

I'm wanting to do the same thing in 2024. Did the enhancement requests work or are we still not able to do this easily?

I have 5 separate trackers for team members that all have 10-15 sheet summary formulas. I need to create another sheet to sum these formulas to present on a Dashboard - I have these in a sheet summary REPORT but that isn't enough...

Thanks,

Paul Newcome

@Dan Tanner Why is the report not enough?

Dan Tanner

https://community.smartsheet.com/discussion/comment/414469#Comment_414469

@Paul Newcome I need to present these on a Dashboard as a Metric, which I can only do using a sheet (Grid data, or sheet summary data).

I know I can make a formula in a helper column or separate data, but as mentioned earlier, it kind of defeats some of the purpose of sheet summary IMO...

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.