Copy the Pivot value to out off the Pivot range?

[imfarhan]

Hi All Excel Guru

I'm not good in MsExcel, I have a report which daily updates and Pivots updates too.

The total count values of one coulum(at the bottom of pivot) , I would like to copy out off the pivot range.
I was using formula using the source of total cell , but pivot refresh every day and the total value cell position get changes.

Could you please help me on that

Many thanks
Farhan

 

[RoryA]

You should be able to use the GETPIVOTDATA function for that but we would need to know which version of Excel you have and which total it is you want to return (specific field names).

 

[imfarhan]

Hi Rory,
Thanks for swift response, I'm using Ms Excel-2003
The pivot, has got field placed in DATA area, where it keeps the count of each row, at the bottom I have got total of that counts

So, I would like to copy of that TotalCount which is not the actual data field instead aggregate total of the field (StudentID).

I tried use the function GETPIVOTDATA,
=GetPivotData("Grand Total",???,????)
The second , paramter said the cell position but it varies when ever the pivot get refresh and 3 paramter is optional in my case so I dont want to use.

Thanks is advance, I wish I could send you the sample file which would be easy for you to understand as my English is not good so apologies for any confusion.

Farhan

 

[RoryA]

If you have the Pivot Table toolbar visible (View-Toolbars-Pivot table), the last button on it is the Generate GetPivotData button. If you make sure this is pressed, you can then type = and then select the cell you want and the relevant GetPivotData function should be created for you.

 

[imfarhan]

Thanks Rory,
After playing with the function GETPIVOTDATA works ok and the problem has been sorted

Many thanks again , and have a good week end.

First time , I came on this website/forum and found it quite useful

Regards
Farhan

 

[RoryA]

Glad to have you with us, and have a good weekend yourself!