Master sheets - why sheets and not reports?

Bill Byrne

New to SS.  Setting up many project sheets and then want the high level info pulled together onto a master sheet.  Videos I've watched say to create a master sheet and link data in from project sheets.  Project management webinar I watched has a company that designs/fabricates/installs 10,000 signs per year (complicated electric signs, not just printed).  So this company must start and also finish 30 to 40 projects per workday (so 60 to 80 projects total daily).  Linking seems clumsy to me, especially with high project volume like the sign company, because it is manual.  You would be highlighting cells, going to the master sheet, highlighting cells there then linking.  Or removing the projects which would include links and then removing blank rows.  I'm assuming it's unlikely all the new data would be on consecutive lines on one sheet so all can be linked in one shot.  More likely each sign may have it's own project sheet so you would be going from sheet, to sheet, to sheet to set these up on the master/remove them.  Then if you follow something like SS's Project Portfolio Rollup system there are 5 master sheets so you would have links that need to be established/removed in additional locations.  For some reason some of those mastersheets are sheets and other reports but I don't understand why. 

 

My question is why ever use a sheet for the master and not always use a report?  When run, a report could automatically find all of the info from hundreds of project sheets with no linking.  Also I've seen complaints about links going one way, if I want to update something I have to update it on the project sheet (which then updates master) but I can't update directly on master.  However reports are two way, I can update on either the project or master sheet and the other will update.  Thanks for any help in advance!

Shaine Greenwood

Hi Bill—you bring up a good point about cell linking in a sheet vs using a report. 

 

Cell linking into a "master sheet" allows you to create formulas on said sheet to perform calculations, if needed.

 

Using a Report is the preferred method (and typically easier to set up), but you can't perform calculations on data in the Report.

Spencer Marlow

Bill, having scanned your post, I think you are 100% right, you should be using a Report, not cell linking.

 

A very simple trick we implemneted is to add a "Roll up" tick box column as standard across all your project sheets. The Report then simply rolls up rows across all sheets where this field is ticked. Makes it very easy and flexible to include / remove rows from the Report.  

Helene Delgado

Hi Spencer -

Can you show an example of the "roll up tick"?

 

Thanks...Helene

Spencer Marlow

Sure ... it really is as simple as it sounds. Below is a screen shot of the top left hand corner of one of our project plans. You see the "Roll Up" column with a couple of ticks in it. We have a separate "Activity Overview" report which uses this tick box as the main field on which to roll up the key elements (rows) from each project into on overview for an entire region or business unit.

 

You can decide at sheet level what consitiutes a "key element" (which can vary from project to project) simply by ticking the box to add or remove that row from the main report.

 

I should add that each project sheet must contain a minimal number of standar, mandatory fields that make the reporting meaningful.

 

Did this answer the question?

 

Boomba

Bill - There is a 5000 rows-per-sheet limitation in Smartsheet, which you might need to think about as you build out your solution.

 

One thing I do is use Spencer's technique above, but label the column "Active?" This box is checked for all new projects. When a project is complete, I uncheck it.

 

Then when I run a report, I only run it for lines where "Active?" is TRUE. This limits my reports to only active projects. (And if I want to report on completed projects, that just requires me to test for that field to be FALSE.)

 

One other tip: Each Smartsheet project sheet I create adheres to the file naming convention "YYYY.MM - projectname". This makes it much easier when trying to select a group of sheets to report on in the Report Builder. All the projects from a single month will be grouped together, and easier to locate in that itty bitty "Where?" list in the Report Builder window.

Helene Delgado

Thanks Spencer!

Innov8@sbpubliclibrary

Making a report is easer, but you lose the card view which I love to use to check for resource overloading. When you link several sheets into a master, does the master still retain the Card View ability?