Summary Sheets

[Lu Rounsaville]

HelloWe have several summary sheets that feed reports and dashboard material. All was great until about a month ago when we noticed the summary sheets that contain formulas that reference other active sheets are not updating until you open them. As I mentioned, these feed into reports and dashboard graphs, so when nothing is updated, people are upset and I am left to open each sheet where I can see it update then.

This one is meant to look up sales for each week when it is entered into the main sheet via a form. Then the summary sheet with the above formula should automatically update (even during the day) because it feeds a graph that is projected on a TV in the office.

@Paul Newcome, it is odd and we have found it's happening on more than one summary sheet. I thought it was isolated until I mentioned the issue in a meeting and others have told me they experience something similar.

I opened a support ticket, but I can't close this pop-up no matter what I click or what browser I use and I'm wondering if anyone has any insight. I'm LOCKED OUT of my work because of some stupid UI refresh alert. ?

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.

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

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.

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?

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

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

I've been using SmartSheet to track progress of different business units on a particular project. Each unit has its own sheet containing grid data that they fill out, as well as sheet summary data where I calculate different KPIs (e.g. number of records with specific status). For each unit, the sheet summary data is then summarized in a report which I use as a source for a unit-specific dashboard.

What I would like to do now is to aggregate the same information on a global level (for all units). I created a report that pulls in the sheet summary data from all the unit sheets. In the report, I added summaries to give me a global sum for each KPI. However, I'm not able to use it as a dashboard source - the Total row will not stay collapsed and the dashboard will always show the data on unit level instead of a consolidated one (please see below).

Unfortunately, this is not the problem. What I have is a report that is based on sheet summary data of different sheets. The report includes totals of all the relevant KPIs (e.g. sum of revenue from all individual sheets) in the top row. I would like to use this report as input for charts on a dashboard. Unfortunately, the report stays expanded and any charts I create show the individual data from the sheets instead of a consolidated total.

Thank you for clarifying that you are looking to source a Chart type of widget from that top summary row. Chart Widgets cannot currently use the Grouping & Summary feature in Reports as the source data (as you've found).

I have 2 sheets and want to be able to pull the value from a sheet summary field on Sheet A into a cell in the grid of Sheet B. Is there a way to directly reference this? I know how to cell link from a grid cell of Sheet A into Sheet B but not summary field.

I guess I could set up a helper column in Sheet A to pull in the Summary field into the grid, and reference this, but there are several Summary Fields in Sheet A I need to access in Sheet B and therefore wanted to avoid this 'workaround' if possible?

I'm trying include a date range with counting the number of applicants within various depts, in certain date ranges, but it's saying incorrect argument set. =COUNTIFS(DISTINCT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], AND(@cell > DATE (2023, 9, 30), @cell

Create a new project. Each project you create is automatically added to the associated blueprint summary. Profile data values from your project sheets are cell-linked to matching columns in the blueprint summary sheet.

You can have more than one Summary sheet with different profile data columns. Each sheet might serve different audiences. Multiple summary sheets can help you manage sheet limitations, which is useful when you're creating large numbers of projects.

I have an excel workbook where I keep track of production, each day we make one of several products, and the calculations, instructions, and notes of a given batch are all stored in a single sheet. All sheets are structured the same (e.g. batch room temperature is in always in cell B6, and Viscosity is always in cell B23).

What I would like is a simple way to have a summary table (in a separate sheet), where after each batch is added, it would be easy to update the table. I would like to enter the batch name, which is similar to the name of the sheet, and have excel automatically pull data from the relevant cells (eg, from B3 for Colom B, from H12 for Colom I).

The INDIRECT function can do this IF you change your sheet naming convention to use underscore instead of hyphens. (i.e., MB_1_9_20 instead of MB-1-9-20) For mysterious reasons (to me at any rate) the hyphens caused #REF errors. As soon as I changed the sheet name, no problem.

Yes, the suggestion from Hans got around the problem of your use of hyphens in the tab names, by inserting the single quote mark at the start of the string containing the tab's name in the INDIRECT function. It still is worth realizing, that hyphens seem to create problems when used in some names.

I thought it would be easy to show how INDIRECT worked, because I have a similar summary page in a workbook of my own; in fact I make extensive use of INDIRECT for a variety of purposes. In your case, I was surprised that I repeatedly was failing, getting the #REF error, until I changed the name just as an experiment.

3a8082e126