Linking Summary Sheet Field Data Into an Existing Sheet to Report on Multiple Projects

Chad Dixon

Hello -

I will be creating ~35 project plans for 35 customers. All will have the same set of tasks. Within each of those sheets, I will have the same (4) summary fields, that capture the following: milestone status, # of outstanding milestones, non-milestone status, and #of outstanding non-milestones.

From there, I am trying to roll all of the data into an existing sheet that provides all of this data across all customers. From a management perspective, the idea is that you can review all milestone/non-milestone task status across all customers.

On the existing sheet it looks something like this:

Milestone Status #of Outstanding Milestones Non-Milestone Status #........

Client 1

Client 2

Client 3

Client 4

Any idea how I can aggregate all this data.

My goal are to give our management team visibility on 1) how all teams are doing, and, 2) create a work flow that sends us a weekly status report if any clients hit a red status.

Thanks,

Chad

SoS | Dan Palenchar

I can't quite picture what your system is and what you want for an output. It sounds like you have 35 sheets, each with the same set of 4 summary fields. A sheet summary report might suffice, or if the sheets are all the same a row report could work that references your entire workspace.

Lorraine Metler

@Chad Dixon I'd love to know if you were able to find a solution to your problem. I have a very similar situation where I am attempting to create a summary sheet from a number of other sheets and detail high level budgetary information.

Chad Dixon

Hi Lorraine -

I did find a solution, and used Dan's recommendation above by utilizing a sheet summary report. To use this option, I had to create the same summary fields for each project plan. Once that was complete, creating the report is pretty straight-forward. I have client names listed as well to the left, but removed their names for confidentiality reasons.

Also, I then entered formulas based on my specific criteria. Hope this helps.

Bethany

I have a similar issue --- Any advice would be appreciated!

I currently house all information (financial etc.) related to hundreds projects in one 'master' sheet. I also have individual project schedules for those projects, linked to the master.

Is it possible to create a 'Total Late Tasks' column in my master sheet and pull in the 'Total Late Tasks' summary sheet field from the individual project schedules?

Andrée Starå

Hi @Bethany

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could link the individual Sheet Summary to a section or cell(s) in the sheet and then reference it in the Master Sheet with cell-linking or cross-sheet formulas.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!