Roll up summation of multiple sheets

08/07/20
Accepted

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

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

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

Sign In or Register to comment.