Link Separate Smartsheets into a 'super sheet'

Barry Seymour

I have a manager who is literally managing over 150 projects. He has a SmartSheet with a list of them all and a few simple notes. I'll call it his "Project Summary Smartsheet." He neither needs nor wants the entire detailed breakdown of each individual project; he just wants the overall project view.

 

Is there a way to link this sheet to the SmartSheets for each individual project in such a way that the completion percentage of the project can be reflected in the manager's Project Summary Smartsheet?

 

Thanks,

 

Barry Seymour

Goldrich and Kest

 

Elaine Chan

I'm looking to do exactly same thing! It is so difficult to manage each smartsheet and I need a master overview sheet that goes horizontal like this. Hopefully someone knows?  

Spreadsheet Name	Task 1	Task 2	Task 3
A	Date	Date	Date
B	Date	Date	Date
C	Date	Date	Date

Jim Hook

There are two possible ways to do this in Smartsheet. The first way that I use most often is to create a separate project summary sheet where one or more rows will be dedicated to each project. Then I link into that sheet the rows from each of the individual project sheets. If all the linked in rows/cells on the individual project sheets are contiguous they can all be linked in in a single operation. The advantage of this way is that calculations can be done on the linked-in results. One possible limitation is that only 5000 incoming links per sheet might not be enough if each of the 150 projects has more than 33 cells to be linked in.

 

The second way is to use a Smartsheet report that scans all the individual project sheets and extracts the data of interest into a single report. I often end up dedicating a column on each individual project sheet that indicates which rows to include in the report. The disadvantage of this approach is that reports cannot include calculations on the reported data. 

Barry Seymour

I'm totally new to Smartsheet, so I'll have to research the functionality you describe where a cell from one sheet is linked to another. Since our plan is to have a single parent summary task for all sheets, can we simply link each project row in the summary sheet to summary / top row of the relevant sub-project?

Jim Hook

Yes you can. Smartsheet's linking capabilities are much advanced compared to Excel or Google sheets. Here's how to do it:

1. On your summary sheet, drag your mouse over all the cells you want to link in from the top row of the sub-project sheet.

2. Right click and select "Link from cells in other sheet".

3. From the list of sheets that you can link from, click on the sheet desired. That sheet will then load.

4. Drag your mouse over the cells you want to link into the summary sheet. You can link anything from a single cell through an array of contiguous cells.

5. Click on Create Link in the bottom left corner.

6. Repeat the above process for each subproject.

 

Your summary sheet will come back and you will see the data linked from the other sheet in the cells you selected. In addition, each cell with linked data will have a little blue triangle in it indicating that it is linked. You can link up to 500 cells at a time with a maximum of 5000 incoming links on a sheet. After the above is done, anytime one of the linked-in cells changes it will automatically update the summary sheet. However, you will need to refresh the summary sheet to see the new data if it was already open. If there is new data you will get a notificaiton at the top of the sheet with a little blue circle indicating that cell link has made changes and needs to be refreshed.

Barry Seymour

So kind, thanks for the help!!!

 

Barry

Mendy Burns

Hey Jim, 

 

I followed the steps you outlined in the comments above however, the line comments and attachments are not carrying over. With project management of several sheets, for partners and clients, how do I ensure any comments or attachments carry back over to the 'Master' sheet?

Jim Hook

Mendy, I think the only way to have the row coments and attachments carry over to the Master sheet is to use the report generator instead of extensive linking. I just tried it and it works great. You will need to generate a report that scans all the project sheets but just brings over the summary row from each one. After you do that the comments and attachments will be brought over with each summary row and can be displayed like on the original sheets. The only challenge I see is having the report figure out which row on each project sheet to make part of the report. In a couple of cases I've had to add a column to the project sheet named Report, for example, that says something like "Yes" in the row on each project sheet you want the report generator to bring over to the Master. That will be time consuming up front but will only have to be done once on each project sheet. Then on the report builder select every project sheet and then in the What box select Report is equal to Yes. You will need to add the columns that you want to bring over from the project sheets, and reorder them possibly by drag/drop and sort the report on the project name column to get them displayed alphabetically for ease of vewing.

 

I hope this works for you. If you haven't used the report generator before it will be a little tricky at first but it works really well.

Mendy Burns

I'll definitely check it out! Thanks Jim!!

Michael R.

Jim. I just wanted to say THANKS for your advice that helped our peers a while back. It also helped me specifically with your BRILLIANT idea about adding a YES column and ticking off the rows to include in a report and how to run it. GREAT idea and application. Thanks again!

jgutierrez111926

I too am new and adding this report column works beautiful! 

 

Thank you Jim