eibi
Active Member
I have set up PivotTable1 on Sheet 1. The Pivot Table automatically provides a Grand Total in the Bottom Right cell. This Pivot Table references a set of data that is frequently updated, so each time the table is refreshed, it has a different number of rows -- sometimes more, sometimes less...
On Sheet 2, I want Cell B2 to reference the Grand Total from the Pivot Table. Yet because my table is frequently changing, I don't know how to build a formula that references the "Grand Total" - it's a 'moving target'.
I assume that the simplest way is to use an INDEX(PivotTable1,2,4) function...but I can't seem to reference the table correctly. This option also creates a secondary problem when the number of rows in the table changes -- because my formula doesn't automatically update.
I have already considered using =VLOOKUP("Grand Total",2,PivotTable1,FALSE) but again, I can't get that to work - is the PivotTable Name shown in the ribbon not the appropriate way to reference the table?
Using Excel 2010. Any suggestions?
On Sheet 2, I want Cell B2 to reference the Grand Total from the Pivot Table. Yet because my table is frequently changing, I don't know how to build a formula that references the "Grand Total" - it's a 'moving target'.
I assume that the simplest way is to use an INDEX(PivotTable1,2,4) function...but I can't seem to reference the table correctly. This option also creates a secondary problem when the number of rows in the table changes -- because my formula doesn't automatically update.
I have already considered using =VLOOKUP("Grand Total",2,PivotTable1,FALSE) but again, I can't get that to work - is the PivotTable Name shown in the ribbon not the appropriate way to reference the table?
Using Excel 2010. Any suggestions?
