Automated / Linked Rows

James Clouthier

Hi everyone,

I'm managing several workstreams, each with their own project plans, and have been asked to create a program-wide plan point of view. To do this - I'd like to create a new sheet and have full rows pulled from each of the workstream plans into the program view, as well as link them so as workstream level updates are made (status, owner, comments, etc) they appear in real-time at the program level.

Is this possible? Can anyone share a template? Thank you!

Genevieve P.

Hi @James Clouthier

Instead of creating a master-sheet, it actually sounds like creating a Report would be better for you! This would be able to bring rows from all your sheets into one place, updating as your sheet updates (including comments, attachments, etc). Reports also include a hyperlink back to the original sheet per-row.

Before creating a Report, make sure all of your Project Plan sheets are configured the same... for example, exact same column Names and column Types with consistent data (so for a Status, all saying "Complete" versus some saying "Completed"). This will help with your Report configuration.

Here's a webinar on how to create Reports: SmartStart: Reporting

If you need help with this after reviewing the webinar, it would be useful to see a screen capture of one of your Project Plans, but please block out sensitive data.

Cheers,

Genevieve

James Clouthier

This is a really interesting idea! Will users be able to manipulate the view from say, the grid view to the Gantt chart view? Or maybe I'd use widgets to show both views side-by-side?

I was able to use links to and from the sheets to have a rolled up point-of-view, but where I'm going to struggle is when the project level plans have rows added/removed, it might create some linkage errors.

I also need it so only certain rows (parent tasks or payment milestones) roll up into the program view, where a more senior audience who want the double-click ability to dive deeper into the weeds will be reviewing progress.

Great ideas - thanks for sharing. If there's any more inspiration, please share!

Genevieve P.

Hi @James Clouthier

Yes! Reports can be seen in either Grid or Gantt view, no problem.

Or in a Report Widget you can choose to Show or Hide the Gantt for your users (see: Using Report Widgets):

Keep in mind that if you're putting it on a Dashboard, Report Widgets won't show the Attachment or Comment columns, so you may want to Publish the Report and use a Web Content Widget instead.

When rows are added/removed from the underlying source sheets, they will be automatically added/removed from the Report as well. This is why I believe a Report is your best solution. 🙂

In regards to the specific rows you want pulled up, we can use the Filter option in a Report to filter specific rows. (See: Use Filters on Reports)

You will need to have some sort of indicator on the sheet that this row is important (for example, a checkbox checked). This can be done manually if you know random, specific rows that should be brought in, or we could potentially use a formula to check the box if it's a standard type of row (ex. all Parent level rows).

Let me know if you need help with those formulas! It would be useful to see a screen capture of your source sheet(s) but please block out sensitive data.

Cheers,

Genevieve

James Clouthier

Wow - this is incredible! Thanks for the advice! Let me take a stab at it - we're actually still in the process of standardizing the next PI set of workstream project plans - so once I have the framework set up, maybe I can add some screen shots of how I've connecting things!

Thanks again!

Genevieve P.

No problem at all!

Glad to hear you're standardizing it, that will make your life a lot easier when looking to do these higher-level views.