Referencing last row in pivot table

greenbag

New Member
Joined
Mar 2, 2015
Messages
8
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top