Referencing last row in pivot table

[greenbag]

Hi,
I have a pivot table that I can expand or collapse and an adjacent simple formula to calculate the difference between each row total and the grand total. i.e. =SUM(C6-$C$14) =SUM(C7-$C$14)

What I want to do is to expand the pivot table and for the formula to still reference the last row in column C. As it is not part of the pivot table when it expands the formula still reference cell $C$14 and so the formula has to be manually adjusted to display the correct result.

How can I change the formula so that it looks something like this? =SUM(C6-Last row in column C)
Thanks in advance for any assistance.

 

[steve the fish]

See if this works for you. Instead of using C14 use the grand total cell you require ie when building the formula click the grand total cell in the correct column. You should then see the GETPIVOTDATA function which will reference the grand total of that column in the pivot regardless of if data is added/ removed etc. That assumes you will always have that column in the pivot or you will get errors.

 

[greenbag]

Thanks, works okay for the grand total.
Easier than I thought.
One further question from this then is when a field expands can you adapt the formula to look at the different between a row and its parent rather than a row and the grand total?