GetPivotData Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Takeadoe - 23 Jul 2007 15:34 GMT

=GETPIVOTDATA("PrehuntTotal",'pvt prehunt ASMtotal'!$A$3,"COUNTY",$AF
$2)

Those familiar with the GetPivotData function know that "pvt prehunt
ASMtotal" is the name of the pivot table and "PrehuntTotal" is the
variable name - the data I'm interested in. The value for "County" is
set dynamically. The user selects county from a drop down list. The
choice is placed in Cell $AF$2. My question for the group is this -
would there be a way to set the variable name the same way that the
value of county is set so that the user can select the variable of
interest on the fly?

Very best,

Mike

Reply to this Message

Bernie Deitrick - 23 Jul 2007 18:23 GMT

Mike,

AFAIK, not from within the GetPivotData function - the datafield can only be passed as a string, not
as a cell that contains the correct string, unfortunately.

You could set up one GetPivotData for each data field, and then use a VLOOKUP or MATCH function to
pull the desired value from the set of GetPivotData functions...

HTH,
Bernie
MS Excel MVP

> =GETPIVOTDATA("PrehuntTotal",'pvt prehunt ASMtotal'!$A$3,"COUNTY",$AF
> $2)
[quoted text clipped - 11 lines]
>
> Mike

Reply to this Message

Takeadoe - 24 Jul 2007 19:20 GMT

On Jul 23, 1:23 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> Mike,
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Hey Bernie - Did you happen to see Debra's post? What does AFAIK
stand for? Thanks for taking time to reply.

Mike

Reply to this Message

Bernie Deitrick - 24 Jul 2007 19:37 GMT

Mike,

AFAIK = As Far As I Know....

And, yes, I saw Debra's post, who happens to be clearly better with Pivot Tables than 100% of the
rest of the world....

Bernie
MS Excel MVP

> On Jul 23, 1:23 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
> wrote:
[quoted text clipped - 34 lines]
>
> Mike

Reply to this Message

Debra Dalgleish - 23 Jul 2007 18:56 GMT

With the variable name in cell A2, concatenate an empty string to the
cell reference:

=GETPIVOTDATA(A2&"",'pvt prehunt ASMtotal'!$A$3,"COUNTY",$AF$2)

> =GETPIVOTDATA("PrehuntTotal",'pvt prehunt ASMtotal'!$A$3,"COUNTY",$AF
> $2)
[quoted text clipped - 7 lines]
> value of county is set so that the user can select the variable of
> interest on the fly?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Takeadoe - 23 Jul 2007 19:07 GMT

> With the variable name in cell A2, concatenate an empty string to the
> cell reference:
[quoted text clipped - 16 lines]
> Debra Dalgleish
> Contextureshttp://www.contextures.com/tiptech.html

Thank you Debra!

Reply to this Message