Need help getting data from large table (get pivot data?)

[MikeZz]

Hi,
I have a table that is about columns by up to 65k rows.
I have a pivot table based on this giant table.

I am looking for a quick way to get summary data if I'm searching up to 15
specific columns out of a possible 30....
The 30 are the total possible columns I may want to search but I would only
need to search a max of 15 columns for any given operation. The problem is
that those 15 could be different each time.

I currently use macros to change autofilters have a Subtotal(109,...)
formula and get that value but this is very slow.

I was wondering if there is a way to extract this data using something like
Get Pivot Data....

Again the difficulty is that I may need to filter on up to 15 columns at a
time but those 15 columns might be different each time.
My understanding of Pivot Tables is that you have to pre-build a pivot and
then look for data in the pre-exisiting table. This wouldn't work for me
because I can't have a pivot with 30 column fields open.

If this doesn't make sense, please let me know.
Thanks for any help,
MikeZz

 

[Bernie Deitrick]

Mike,

You can construct a formula like this, where you want to sum of NumData from
the pivot table starting in cell A3 sheet SheetPT, where Field1 is "Entry 1"
and Field2 is "a value"

=GETPIVOTDATA("NumData",SheetPT!$A$3,"Field1","Entry 1","Field2","a value")

You can see the syntax by selecting a blank cell, typing =, and selecting a
cell from the privot table.

You can also use cell references like so:

=GETPIVOTDATA("Value3",Sheet4!$A$3,A1,B1,A2,B2)

where cells A1:B2 contain

Field1 Entry 1
Field2 a value

HTH,
Bernie
MS Excel MVP