What is the best way to consolidate data from multiple sheets?

Sam S

I have multiple projects running side by side and I need a method to view a high level overview of all my projects with real data and a Gantt chart. I know I can use reports but reports do not support Gantt charts or a calendar. Does anyone have a better solution for how to do this?

JamesR

SamS,

Yes as you quite rightly say reports do not supply the Gantt chart element. Its not ideal but if you able to geth the same rows by filtering you will still get the Gantt Chart. Alternativly for standard reporting, you could create a linked sheet using Cell linking with the specific Rows and Columns you need in your report.

Both these suggestions depend on the complexity of your Programme and the output required.

Travis

To follow up on what JamesR said, you can create a “master” overview Gantt chart using Cell Linking. To set something like this up, start by creating a new, blank project sheet to serve as the master. You can then right-click on a cell in the new sheet and select “Link from Cell in Other Sheet” to start pulling in tasks from the individual sheets.

If you’ve not used Cell Linking before, I recommend reviewing the instructions in our Help Center to learn more. We also have a video demonstration in the article that describes how to get started. You’ll find this information here:http://help.smartsheet.com/customer/portal/articles/861579-cell-linking

We’ve also recently published a blog article that describes creating a Master Gantt Chart using Cell Linking://www.santa-greenland.com/blog/support-tip-cell-linking-master-gantt

We are developing the ability view Gantt charts and Calendars in reports - check out our product roadmap for more information on features we are developing://www.santa-greenland.com/product-roadmap

CraigH

I have 6 projects that I have rolled up into a master sheet that I can share with senior management for a high-level view of all projects. My problem: if I add a task to any of the 6 projects it is not automatically added to the master list. Is there a way to do this without having to re-link everyhting?

Thanks.

SashaR

Craig, you might try using reports rather than cell linking which can automatically pull in rows from multiple sheets based on criteria you set (even new rows you add). They don’t support Gantt charts yet but according to the product roadmap, they are working on it.

CraigH

Thanks Sasha, will try that. I'm rather new to Smartsheet so any tips and tricks are welcome!

CraigH

I was able to get a report to include the information from all sheets rolled up, but was disappointed that heirarchies are not included in the report. I wanted to be able to give senior management the option of a high-level view with options to drill down.

Guess I will just have to manually link any added cells to the master sheet.

BobbyLite

A trick we use to get parent rows in reports is to add a checkbox column to your sheet and check the boxes of the parent rows and use that as a criteria to pull in rows to your report. Or you can add a Text/Number column to your sheet and add parent row text to each child row in that column. That way, when you pull in those rows, you get parent row information as well.

Jason Hawker

Folks,

You're solve may be to use SIGHTS for executive managment. If in the case they need to 'drill down' into the actual project plan tasks you can simply link a SIGHTS project widget to the actual project plan URL.

We rollup 20+ projects using SIGHTS, metrics and even high level gantt charts for each then have widgets showing actual project metrics.

lknight32486

Do we have an ETA on when Reports will have Kanban view? The ability to see multiple sheets within a kanban view (Assigned To for my group) is crucial to our day to day.

According to what I can see on line, this topic has been in the backlog since 2015; it is almost 2018. Please move it up in priority, pretty please!

Thank you!