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

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

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • @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
    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
    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å
    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 EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!