How can I get pivot data when not visible/expanded?

[techjunkny]

Hi,

I'd like to get data out of my pivot table via getpivotdata() but I get ref# errors unless all the relevant fields are expanded. The problem is that there are enough item combinations that my pivot table explodes to 500K rows (consuming hundreds of MB of memory). The pivotcache itself is not that big. Is there any way to get the totals -- get data out of the pivotcache -- while keeping all the fields collapsed?

Thanks,
Ben

 

[rabidej]

I have the same question as Ben. If data is not visible, I get a ref# error making the Getpivotdata() formula useless. Has anyone figured out a way to around this or is the solution just not to use the formula?