Creating a project programme from a number of Project plan sheets

Hi All,

I have project plan sheets set up for my project managers to use. each project has 4 phases which have parent rows set up to catch up each phases progress along with a top line project row that captures overall project progress.

I have created programme reports that look at these sheets to show a project by project progress for senior management to review.

What I would like to do is have a top line programme row that rolls up the individual project dates to give an overall programme start/finish progress % etc?

Is this possible within the report functionality on Smartsheets to add a top programme line or am I going to need to build it on a grid with links to cells on the other sheets?

Attached is the report I have made pulling the projects together, it is the top row pulling it all together for programme detail that I am missing.

Thanks,

Greg Peddell-Grant

Find more posts tagged with

Accepted answers

Purnima Gore

I wanted to do the same thing and found it is as you said regarding the grid.

I created line items on the programme plan and then indented them so that the top line programme had the overall date.

I know you are looking at MS Project functionality that allows you to link entire sheets but that isn't available.

Kind regards

Purnima

Eric D

Hi @Greg Peddell-Grant ,

I understand you'd like to create a row within a Report to roll up the overall start/end dates, percent complete, for all your programs for quick visibility. Regrettably, it's not currently possible to create a summary row within Reporting to roll up results from all programs. If you'd like to pass this along to the Product Team for review in future enhancements, you can add your request within our page here: Track Product Updates or Make an Enhancement Request https://help.smartsheet.com/articles/2476161-understanding-product-updates-making-enhancement-requests

One solution where you could achieve this would be to create a Summary of Projects sheet that would be used to capture the top lines from all of your program sheets, and then using Hierarchy, create it's own top line to show the metrics you're targeting(Start/Finish/%Complete). I've outlined steps below:

Since the columns in the new sheet will want to align with your projects, you can simply duplicate one of your project sheets using Save as New, name the Summary sheet, and remove the Sheet data and formatting, so that you're only duplicating columns. See screenshot:

Next, return to your project sheet and within your top row rolling up total metrics, highlight the cells you'd like to link to the Summary sheet, right-click and choose Copy:

Within your Summary sheet, choose the left-most cell for the row, right-click and select Paste Special:

Paste Special dialogue box appears, choose Links to Copied Cells:

The row is created with the linked cells which can be indented to nest under your top line:

Repeat steps above to copy other top line parent rows into the Summary to include all program metrics:

Once you've created your Summary sheet, you can source the top line within a Report, or Dashboard widget.

I do have good news in that at our Engage conference, new Reporting features were announced coming out soon, that will include additional options to Summarize and Group sheet data. More information about our Engage conference and releases can be found here: https://www.smartsheet.com/engage

For links on guidance on the topics used above, please check out the following resources:

Copy a Sheet with Save As New:

https://help.smartsheet.com/articles/803778-copying-a-sheet

Cell Linking:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/cell-linking

Hierarchy: Indent or Outdent Rows:

https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

Widget Types for Smartsheet Dashboards:

https://help.smartsheet.com/articles/518558-widget-types-for-smartsheet-dashboards

Hope this helps!

Kind regards,

Eric

All comments

Purnima Gore

I wanted to do the same thing and found it is as you said regarding the grid.

I created line items on the programme plan and then indented them so that the top line programme had the overall date.

I know you are looking at MS Project functionality that allows you to link entire sheets but that isn't available.

Kind regards

Purnima

Greg Peddell-Grant

Thanks Purnima I thought as much.

Kind regards,

Greg

Eric D

Hi @Greg Peddell-Grant ,

Next, return to your project sheet and within your top row rolling up total metrics, highlight the cells you'd like to link to the Summary sheet, right-click and choose Copy:

Within your Summary sheet, choose the left-most cell for the row, right-click and select Paste Special:

Paste Special dialogue box appears, choose Links to Copied Cells:

The row is created with the linked cells which can be indented to nest under your top line:

Repeat steps above to copy other top line parent rows into the Summary to include all program metrics:

Once you've created your Summary sheet, you can source the top line within a Report, or Dashboard widget.

For links on guidance on the topics used above, please check out the following resources:

Copy a Sheet with Save As New:

https://help.smartsheet.com/articles/803778-copying-a-sheet

Cell Linking:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/cell-linking

Hierarchy: Indent or Outdent Rows:

https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

Widget Types for Smartsheet Dashboards:

https://help.smartsheet.com/articles/518558-widget-types-for-smartsheet-dashboards

Hope this helps!

Kind regards,

Eric

Greg Peddell-Grant

Hi Eric,

Thanks for the response, I have already set up what you are suggesting.

Kind regards,

Greg

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.