Roll up summation of multiple sheets

Lindsey Cunningham

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!

Genevieve P.

Hi Lindsey,

If you wanted one total number, you're right, you would need to go into each sheet and either use a cross-sheet formula to look into each column/cell, or use Cell-Linking to link these top 4 rows from each sheet into your Summary sheet, then do a SUM formula in that sheet itself for your totals. You are also correct in noting that should there be a new manager, you would need to then either adjust all your formulas to include this new sheet or cell-link in this new row.

Alternatively, you could create a Report across all these sheets to pull the row information you need. For example, you could have one Report that just looks for the row # of trips in each of your 10 sheets. When you have a new manager you would find the sheet and include it in the Report for it to update.

Then if you want a SUM you can click on the column name in a Report and a small grey box will appear with that number. Although you are unable to use Reports in formulas or use that SUM figure in a sheet or Dashboard, it would be easily accessible by clicking on that column name:

If neither of these options work for you, you may want to look into one of our Premium Applications called the Pivot App, which can create rollup summaries across multiple sheets by using a Report as its source.

Let me know if I can clarify anything further!

Cheers,

Genevieve

Genevieve P.

Hi @Lindsey Cunningham

You're on the right track! However currently, Sheet Summary Reports cannot be used with any Premium Applications (these include Pivot, Datamesh, Data Uploader, Dynamic View, etc) due to the API not having any endpoints in the Summary Report to pull data from. 

You would want to create a regular Row Report instead to use in the Pivot App. Let me know if this works!

Genevieve P.

Hi Lindsey,

If you wanted one total number, you're right, you would need to go into each sheet and either use a cross-sheet formula to look into each column/cell, or use Cell-Linking to link these top 4 rows from each sheet into your Summary sheet, then do a SUM formula in that sheet itself for your totals. You are also correct in noting that should there be a new manager, you would need to then either adjust all your formulas to include this new sheet or cell-link in this new row.

Alternatively, you could create a Report across all these sheets to pull the row information you need. For example, you could have one Report that just looks for the row # of trips in each of your 10 sheets. When you have a new manager you would find the sheet and include it in the Report for it to update.

Then if you want a SUM you can click on the column name in a Report and a small grey box will appear with that number. Although you are unable to use Reports in formulas or use that SUM figure in a sheet or Dashboard, it would be easily accessible by clicking on that column name:

If neither of these options work for you, you may want to look into one of our Premium Applications called the Pivot App, which can create rollup summaries across multiple sheets by using a Report as its source.

Let me know if I can clarify anything further!

Cheers,

Genevieve

Lindsey Cunningham

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!

Genevieve P.

Hi @Lindsey Cunningham

You're on the right track! However currently, Sheet Summary Reports cannot be used with any Premium Applications (these include Pivot, Datamesh, Data Uploader, Dynamic View, etc) due to the API not having any endpoints in the Summary Report to pull data from. 

You would want to create a regular Row Report instead to use in the Pivot App. Let me know if this works!

Lindsey Cunningham

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.

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