What Best Practices are there for a roll up master sheet

Joseph Bourgoin

I have two Smartsheets and using cell linking have certain information linked to another sheet. I have it working but I am wondering what "Best Practices" are out there for this type of sheet.

I appreciate any comments and thanks in advance! 

Travis

Hi Joseph -  Reports is a powerful tool that can look across multiple sheets and pull in data based on criteria you specify. This is a great automated way to view what is happening across all your sheets. Here is some more information on creating reports: http://help.smartsheet.com/customer/portal/articles/522214

It's not possible to view Gantt charts or a calendar in a report but this is something we are looking at implementing in future versions of the reporting tool.

You can also create a “master” overview sheet using Cell Linking. Cell Linking will enable you to display start dates and durations from tasks that are found in other sheets. With dependencies enabled on the master sheet, the end date won’t be linked in but it can be calculated automatically from the linked start date and duration (see http://help.smartsheet.com/customer/portal/articles/861579#nolinking for more on cells that don’t allow links).

 

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

Chris Winfield

Hiya,

 

I have a master project list that contains summary information for around 200 projects at the moment. There has been some fairly tedious manual cell linking required to get all of the projects in and linked but the system seems to be working well so far.

 

We basically have a template project sheet and every project is given a reference code generated by the master sheet. At the top of every project sheet is a set of information (project manager, exec sponsor, financial benefits, etc) that all link out to the master sheet.

 

Each time a new project is created we make a new project sheet from the template, create a new row in the master list and then link the cells. It's not quite so tedious when we're only setting up one project at a time, and we run all of our reports off the master sheet.

 

I'd be interested to hear from others abour their experiences and tips.

Michael R.

Chris. Actually, I think this is one of the smartest ways I've read about. I'm going to play w that. Using SS for multiple projects on sheets vs one sheet has the pros and cons but I like your method here. I'll add that I talked w chat support and VLookup is coming and some called Range Cell Linking has some votes. I requested that because currently, w Cell Linking it's useless as a project grows in rows or columns. What if you insert rows into a Cell Linked range? Those cells outside those Row #s are gone. :-( But Range Cell Linking, would give a sort of marker that could grow. Now THAT would be more usable. Anyway, I just wanted to say thanks for your good ideas. :-)