Getting the total of Grand Totals from Multiple Pivot Tables

[Amy33]

I have a spreadsheet that has a number of different data tabs within it, all of these data tabs have a pivot table on an 'Overview' sheet.

Within the overview sheet that contains the pivot tables, I am wondering how best to get the Overall Total of all of the pivot table Grand Totals within the sheet that will then alter when you change report filters within the pivot tables.

My pivot tables are set up with report filters, dates as columns and areas as the rows with the grand total at the bottom of the columns.

Example: The overview sheet has say 6 pivot tables with attendance numbers for different areas, I need to be able to add up all the grand totals of these 6 pivot tables for an overall total. This overall total needs to change depending on the report filter (changing the report filter alters the attendance numbers so should alter the 'Overall Total').

I have put a basic formula in adding the cells together, but then when you change the report filter, the formula becomes invalid and returns an error as some of the cells 'no longer exist'.

Hope this makes sense, let me know if you need anything else. Thanks so much in advance for anyones help!

 

[Laurence83]

One idea, although there possibly maybe more elegant solutions, is to use Vlookup or IndexMatch in each range to return the "Grand Total" from each pivot and then sum them together.