Roll up summation of multiple sheets

I am trying to create a budget sheet that roll up the information inputed by mangers on 10 different sheets.

Each manager has a grid like the one below and I want to duplicate that as a summation of information from each sheet. So a summation of the #of trips for a conference across all the sheets would need to go in the Conference # of Trips cell.

Is there an easy way to do this without manually referencing the sheet and cell for every reference? Currently the only think I can think of is writing a formal to manually do it like: =SUM({Angela Conference # of Trips}, {Courtney Conference # of Trips}, {Kris Conference # of Trips}, {Malinda Conference # of Trips}, {Martin Conference # of Trips}, {PMO Conference # of Trips}, {Ruth Conference # of Trips}) for every single column across and the # of Trips, Days/Night, No. Attending, and Proposed Attendee. Also if a manager is every added every single formula would need to be updated to add the new person. Thanks!

Best Answers


  • Thanks so much! The data needs to be able to be printed out in a report format so clicking on the columns will not work. I'll look into the pivot app.

    I tried creating a sheet summary report with a summary of each of the sheets and then create a pivot.

    When I go to create the pivot based on the above report it won't let me add a row.

    Thanks for your help!

  • Got it. Thanks!

    I think I solved it using the Pivot App.

    For those curious, I reworked the initial sheet so I could use the pivot app and then created a report that pulled all the data from each sheet into one report, created a new pivot sheet with the sums, and then referenced those grand total sums in yet another sheet with formula to calculate the exact costs I needed

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad you sorted it out, and thanks for posting your solution!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!