Masterplan

Taru

Dear All,

Our program consists of 5 sub projects and each of them have their plan. All of these individual plans need to be rolled up into a  consolidated master plan that would finally track the inter-dependencies and the critical path.

I have read previous posts to use the cell linking feature as well as using the reporting feature.

However, if I use the cell linking or the reporting feature the  hierarchy in the individual plan gets disturbed.

At the same time the idea is that the individual PMs will work on their plans and any changes that they do should be reflected in the consolidated plan being monitored by the PMO.

Would appreciate ideas from members on how to solve the above issue..

Many thanks in advance.

Taru

Shaine Greenwood

Hi Taru,

You can use cell linking on a new "master plan" sheet and set up the hierarchy yourself as needed.

I know you're opposed to it initially, but reports might work as well. A master plan is more easily scannable if it doesn't have too much (or any) hierarchy. 

Here's another community post where people have discussed using both options: https://community.smartsheet.com/discussion/sheet-roll

Taru

Thank you Shaine. Some points of the discussion were helpful.

 

It is yet to resolve my problem fully but hopefully will get there.

Kind regards,

Taru

Shaine Greenwood

Hi Taru,

If there's a more in-depth system that you're wanting to work out, you might consider reaching out to our consultants team: https://www.smartsheet.com/consulting

Adam Olbur

Has this been looked into any further.

Smartsheet provides some good templates, however I think it would be cool if they created/provided a PMO suite template. I agree the ability to have a master roll up sheet would be valuable. Reports are good for pulling the information but not manipulating the data to create a nice dashboard view.

Is this something we can discuss further or I can put in a feature request...I think a bunch of PMOs could benefit from working this way?

Paul Newcome

I create a series of hidden columns for each data point that I want to pull/track for each project. My rollup sheet contains these same columns in the same order. I can then quickly use cell linking to pull from the hidden columns on each sheet across a row in my Rollup.

This allows me to maintain live data from all projects organized within the same sheet that can then be used for metrics/dashboards/etc. It also allows me to pull more "high level" type of reports that shows the pertinent data for each project based on my specified criteria such as which are on track vs behind vs live etc.

Adam Olbur

Seems like this discussion does almost everything for what I was talking about, not sure if it needs tweaking based on the PMO set up I described:

https://community.smartsheet.com/discussion/comment/241687#Comment_241687 but I think I will try this for now.

Paul Newcome

Ah. Ok. I think I am following a little better now.

What data exactly are you referring to when you say "the data in the reporting tool cannot be pulled into a rollup sheet to display nicely on a dashboard"?

You have obviously found how to use Reports which can be used to display the actual row data, but are you more referring to the metrics of things for your dashboards such as actual counts?

Adam Olbur

Exactly - the fact that we can't pull metrics out of reports is a little frustrating. However the template set for project intake seems to be the best workaround.

I think SmartSheet is awesome but could be even better with product enhancements such as the metrics piece from reports or linking out from reports into sheets to allow that capability, either way works for me. My thought would be to pull the data into a sheet and allow for counts/metrics, but keep the cell locked so the data cannot be manipulated/or is rather the source of truth for that cell.

Make sense?

Adam Olbur

Hi Paul,

I am sort of stuck and looking for help at a strategy and am trying to build a roll up report and dashboards based on KPIs but also drill down to teams and users.

At any given time we have 30-40 projects in flight in our PMO.

There are 140 or so potential contacts that could be assigned to any project (usually about 20 or so on a project)

The metrics I am looking for are as follows:

Total # of Tasks by user

Total # of Tasks by team (this is easy to calculate if I can get the total count per user)

Late Tasks by user

Late Tasks by team

Totals # of Projects by user

Total # of Projects by team

I have looked at reporting and as discussed I cannot capture counts without a manual process, which would be unbearable due to the nature of our work. I have looked at summary fields to build summary reports but once again this is too tedious as I would need to do this on every single project for 20+ users (also unrealistic).

I am looking through help and forums but have not found anything that quite solves this for me. If I am missing something please let me know.

Thanks - Adam

Adam Olbur

Any update or ideas on this would be appreciated...thanks.

Paul Newcome

I would suggest setting up some hidden columns on each sheet where you capture the project data you need across a single row.

You can then use cell linking to link this data to a Rollup sheet where each project's data has it's own row. From there you can run what metrics you need and updates to projects will be a live update to your Rollup which in turn provides a live update to your overall metrics.