Sheet Roll-Up

I'm curious if there is a way to have a "higher level" sheet that pulls from other sheets.

That way, each individual on our team can have their own projects with greater detail and our supervisor can see a higher level view of everything being worked on.

Find more posts tagged with

Non-profit

Sharing and Collaboration

Marketing

Conditional Formatting

Enhancement Request

Comments

Atus Bartal

Sarah,

I think Cell linking is what you're looking for:

"Cell linking is useful when you want to consolidate information from multiple sheets. Use cell linking to create a master or roll-up sheet, to keep track of cross-project date dependencies, or simply to ensure that values stay current across a collection of sheets without a lot of re-typing."

[see http://help.smartsheet.com/articles/861579-cell-linking]

Robert Jack

Unfortunately, cell linking is a horrendous overly-administratively burdensome mechanism that requires a lot of manual configuration and set up and maintenance.

Competitor products offer sheet roll up functionality that automatically creates a "master" sheet with the various sub-sheets amalgamated or aggregated within it. This is a real producitivy saver that is invaluable to senior PMs or programme managers.

Smartsheet falls way short of the mark on this functionality.

Heidi Decker

Hi Robert,

Have you thought about using reports? With reports you can pull together several sheets into a report with only the information you want your managment to see. Reports can also be viewed as calendars and gantts.

Peace!

Daniel Stein

Hi Sarah,

I agree with Heidi that it sounds like reporting will best serve your needs. Here are some ideas and resources for setting up your reports.

One common pattern for rolling up key sheet information into a report is to add a checkbox column named "Report" to the team members' sheets, and build a report that shows rows where "Report" = checked. Then each team member can check only the rows that they want to appear in the roll up report, while keeping the details confined to their own sheets.

You can also create reports that show management a particular dynamic view of work across the team, e.g., status = At risk, overdue tasks, etc. This support article has more details on how to create a report. Creating a report is often better than using cell links, because reports show dynamic views of multiple sheets and are easier to update in one place.

If you currently use hierarchy and roll-up calculations in sheets, e.g. formulas like =SUM(CHILDREN()), you can build a report that automatically pulls in parent rows only by adding a column to your sheets called "Parent level," where each cell has the formula =COUNT(ANCESTORS()). This formula automatically calculates the hierarchy level of each row, which you can use in a report filter.

Once you've had a chance to try out reports, you may also take a look at Sights (a paid add-on feature), which allows you to create a visual dashboard with key information from sheets and reports, annotations, images, and navigation to drill in.

Best regards,

Daniel

Product Manager, Smartsheet

Tim Meeks

Sarah,

We are doing what Daniel suggested using reports.

We have 6 departmental reports that are updated by team members. All of the sheets have the same columns and dropdowns except for team member names. The teams track the projects as detailed as they would like by using sub or children tasks underneath the parent row.

Only the Parent Row has a project number inserted. We just made up a project number format (###-####) for each department/project. The first 3 digits are the department, last 4 the project #. The user just types them in manually. Only rows with Project Numbers are pulled on a report. And there is also a column titled "Report to CIO" that if checked pulls the project on a Weekly/Monthly Summary REport. This is so that hte CIO only sees important or urgent projects. Then there is are weekly and monthly reports that ALL projects but it only pulls the Parent rows which have a project #. The report is scheduled weekly every Thursday @ 4pm and monthly on the last weekday of the month.

It's been a great tool for project status updates.

Tim

Robert Jack

Hello all,

Reports is a mechanism to aggregate data, agreed.

However, the massive functionality gaps make this unusable for us.

the main thing being that you lose all grouping an the ability to expand / collapse.

Also the fact that the filtering functionality is more or less removed (yes you can then go into the report builder - if you have permission - and edit the report parameters, but this is more limited than normal sheet functionality and is much more laborious.

It shouldn't be so difficult and involve such manual fiddling to make the system perform the most basic of project management functionality. Extra columns, extra valies etc....

Smartsheet need to up the game with reports and make them work like normal sheets or similar, before we could use them in any meaningful way as a roll up option.

Thanks.

Daniel Stein

Thanks Robert for your feedback regarding grouping and quick filtering in reports. We agree that these would be nice enhancements to reporting, and we are considering them for future releases, along with summary calculations in reports.

In the meantime, we have a few features coming soon that should help customers to quickly see the views they want on sheets and reports:

- Saved filters on sheets
- A dynamic "current user" filter on sheets and reports. This will automatically filter a contact column to the person currently viewing the sheet or report.

Thanks again for your feedback.

Best regards,

Daniel

J. Craig Williams

Neat process Tim. I'm going to borrow that.

Craig

Ally

I also struggle with the lack of Master sheet roll-up functionality. One issue that I can't seem to overcome with Reports is adding new Sheets to the Report automatically. Our PMO has new projects submitted to us every few days, which mean that a new Sheet (Project Plan) is created. Because there is no Master sheet roll-up, the new Sheets must be manually added to the associated Reports. Does anyone have suggestions for overcoming this issue?

Dale Murphy

Ally, has anyone made suggestions here? If your report references a workspace or folder instead of specific reports, any sheet in that workspace/folder will automagically show up on a report.

Christie Wiggers

hi i have tried linking to a folder by clicking the folder rather than individual sheets. However the new sheet i added to the folder after the fact is not automatically included. I seem to have to go and edit every report to include any new sheets I have added. Is there something more I am missing here?

klontok

I too have tried using a folder in the "where?" condition for a report and it DOES NOT automatically incorporate new sheets added to that folder. That means a lot of manual hooking up every time a new project sheet is created...

rossport

I have this same problem. I want to run reports on a certain folder, but the sheets in that folder will be changing daily. It is impractical and dangerous to have to rely on people to update the report builder daily, I fear we will miss something here and work off of bad data.

Is there another way to get this functionality?

fallen45266

I have it working for my projects .... However the sheets must be located in a workspace folder and not within a folder folder. If the sheets are in a workspace by clicking on the workspace all sheets within that folder become part of the Report. As new sheets are created in that workspace they will show up on the report. The only problem with the report process is you cannot use functions once the report is active to sum up values such a project amounts or resources. Very limited once the data is in the report. A master sheet with child sheets would be more beneficial so the functions become usable within the master sheet for summarization since the report cannot do summarizing.

D@n

Three years after this post was started and the sheet roll-up functionality is still missing. I agree with Daniel that this is a huge gap. It's making SmartSheet very cumbersome for our PMO.