GETPIVOTDATA used with a central pivot table

GETPIVOTDATA used with a central pivot table - values vanish

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scharf - 26 Oct 2006 19:32 GMT

I have a report template that uses getpivotdata from pivot tables hidden in
the background. There are many templates in use and refreshing each one was
becoming very time consuming. I hoped to use one central set of pivot tables
and point all of the indivual reports to this workbook using getpivotdata. I
discovered that the data vanishes unless the central "pivot sheet" is open.
Is there any easy way around this? Many thanks in advance for your
willingness to share your acumen.

Reply to this Message

Jim Thomlinson - 26 Oct 2006 20:10 GMT

Nope the pivot has to be open in order to read the data out of it with
GetPivotData. That being said any number you can generate with a pivot table
can also be generated with a SumProduct Formula wich does not require the
open workbook... Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Signature

HTH...

Jim Thomlinson

> I have a report template that uses getpivotdata from pivot tables hidden in
> the background. There are many templates in use and refreshing each one was
[quoted text clipped - 3 lines]
> Is there any easy way around this? Many thanks in advance for your
> willingness to share your acumen.

Reply to this Message

scharf - 30 Oct 2006 21:43 GMT

Thanks Jim. The pivot tables that I use are pulling data from Access
queries. The queries pull from linked tables (ODBC to SQL). I am unable to
guarantee that the field in "column C" will remain there (e.g. the data is
very dynamic). Will this approach fit into that model? I also need to pull
back the total number of records that satisfy 3+ criteria... I am not very
familiar with sumproduct, so I want to be sure that this method will ring the
bell. Thanks very much for your guidance.

> Nope the pivot has to be open in order to read the data out of it with
> GetPivotData. That being said any number you can generate with a pivot table
[quoted text clipped - 10 lines]
> > Is there any easy way around this? Many thanks in advance for your
> > willingness to share your acumen.

Reply to this Message