summary of sheet summaries

edited 12/09/19 in Using Smartsheet
12/07/19 Edited 12/09/19

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.

Comments

  • Andrée Starå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

    work-bold

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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.

  • 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.

  • I need to do the same

  • Has anyone submitted a product Enhancement to Smartsheet?

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

  • 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 NewcomePaul 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.

  • 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 NewcomePaul 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.

  • @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.

Sign In or Register to comment.