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

07/15/20
Answered - Pending Review

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

Answers

  • SoS | Dan PalencharSoS | 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 MetlerLorraine 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.

  • 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.



  • 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å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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.