Reference a pivot Grand Total when colum reference changes

[LCullen]

Hi,
Apols if this query has been answered elsewhere but I've been unable to find anything.

I have a limited pivot table of currently 16 rows and 6 columns A-F. My grand total is currently in column F and contains the oldest date found in the cells along its row. See example below. The most volatile column that only 'sometimes' has data is column E (Type 4). The other 3 will always have data.

In my spreadsheet, beneath the pivot table, is a static representation of this table which is used in a Power Point presentation. Its cells are linked to the pivot. This works perfectly will until I get to the Grand Total which can change from month to month. This month it was in column F but next month could be in column E because of the dynamic nature of the data in column E.

Is there a way to ensure the data in the Grand Total column (whichever column it may be) is always returned to the static column in the PP presentation table? The ideal would be to move the pivot Grand Total to column B but you can't do that .

Hope this makes sense and any help would be appreciated as I've hit a bit of a wall on this one. I did attempt to use GETPIVOTDATA but must be doing something incorrectly because I kept getting a #REF error.

Thanks in anticipation.

L


[TABLE="width: 519"]
<tbody>[TR]
[TD]Pivot table
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Min of the stuff received
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Type 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Type 4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]alkjdhfl
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type 3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lakjdhflkahf
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 1
[/TD]
[TD][/TD]
[TD="align: right"]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/09/2017
[/TD]
[/TR]
[TR]
[TD]Name 2
[/TD]
[TD="align: right"]16/03/2018
[/TD]
[TD="align: right"]21/02/2018
[/TD]
[TD="align: right"]08/09/2017
[/TD]
[TD][/TD]
[TD="align: right"]08/09/2017
[/TD]
[/TR]
[TR]
[TD]Name 3
[/TD]
[TD][/TD]
[TD="align: right"]20/09/2017
[/TD]
[TD="align: right"]13/09/2017
[/TD]
[TD][/TD]
[TD="align: right"]13/09/2017
[/TD]
[/TR]
[TR]
[TD]Name 4
[/TD]
[TD][/TD]
[TD="align: right"]19/02/2018
[/TD]
[TD="align: right"]14/09/2017
[/TD]
[TD="align: right"]25/02/2018
[/TD]
[TD="align: right"]14/09/2017
[/TD]
[/TR]
[TR]
[TD]Name 5
[/TD]
[TD][/TD]
[TD="align: right"]08/02/2018
[/TD]
[TD="align: right"]07/12/2017
[/TD]
[TD][/TD]
[TD="align: right"]07/12/2017
[/TD]
[/TR]
[TR]
[TD]Name 6
[/TD]
[TD="align: right"]21/02/2018
[/TD]
[TD="align: right"]15/02/2018
[/TD]
[TD="align: right"]13/12/2017
[/TD]
[TD="align: right"]13/03/2018
[/TD]
[TD="align: right"]13/12/2017
[/TD]
[/TR]
[TR]
[TD]Name 7
[/TD]
[TD][/TD]
[TD="align: right"]28/02/2018
[/TD]
[TD="align: right"]14/12/2017
[/TD]
[TD][/TD]
[TD="align: right"]14/12/2017
[/TD]
[/TR]
[TR]
[TD]Name 8
[/TD]
[TD][/TD]
[TD="align: right"]14/12/2017
[/TD]
[TD][/TD]
[TD="align: right"]02/01/2018
[/TD]
[TD="align: right"]14/12/2017
[/TD]
[/TR]
[TR]
[TD]Name 9
[/TD]
[TD][/TD]
[TD="align: right"]14/02/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/02/2018
[/TD]
[/TR]
[TR]
[TD]Name 10
[/TD]
[TD][/TD]
[TD="align: right"]14/02/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/02/2018
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

In my spreadsheet, beneath the pivot table, is a static representation of this table which is used in a Power Point presentation. Its cells are linked to the pivot.

Maybe make your links more dynamic? So if say F20 is linked to the grand total in E2 or F2, check the header in row 1 and return the one that is the Grand Total.

=IF ($E$1="Grand Total", E2, F2)

 

[LCullen]

Hi - thank you for your response. Doesn't the formula still presuppose that the Grand Total of the pivot table is always going to be in column E. What if, as I explained, the Grand total of the pivot in next months run moves to column F? Sorry for sounding a bit dim but my issue is the dynamic nature of the positioning of the Grand Total column in the pivot.

 

[AlphaFrog]

=IF($E$1="Grand Total", E2, F2)

The example IF formula checks if the header in E1 is "Grand Total". If it is, the formula returns the value from cell E2. If it is not, it returns the value from F2. The point is, the formula determines which column is the grand total.

 

[LCullen]

Hello again,

I am a dimwit. Of course this simple "if"should work. Clearly my brain wasn't in gear yesterday. For some reason I was expecting 'convoluted'. Doh! Thanks again.