Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Sam S
Sam S
edited 12/09/19 in Archived 2015 Posts

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
    JamesR ✭✭✭✭✭✭


    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
    Travis Employee

    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: http://www.smartsheet.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: http://www.smartsheet.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?



  • SashaR
    edited 05/15/15

    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



    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!

This discussion has been closed.