Getpivotdata - able to reference to another cell all parameters but not the datafield

[Roger The Rabbit]

Hi

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

In the above format of the GetPivotData I am able to reference all the
Item and Field names but not the Data_Field

Example


Data_field = "Amount" in Cell A3
field1 = "Income" in Cell A2
Item1 = "Account Type" in Cell A1

The Following Works

=getpivotdata("Amount",CashFlow!A7,A1,A2)

But When I try and use a reference for the data_Field Then I get a
#REF!

=getpivotdata(A1,CashFlow!A7,A1,A2)

I tried using INDIRECT() , Enetering "Amount" in cell A1 with the "


Can anyone help.

Thanks

Roger

 

[Roger Govier]

Hi Roger

Field A1 repeated twice instead of A3??

 

[Roger The Rabbit]

Hi Roger Govier

No its not repeated , it just does not get data and comes up with #Ref!

So if data_field is enetered into the formula as the data like "Amount"
it gets the total requested, but when "Amount" in the Formula is
replaced by a reference to another cell that does contain the exact
same text "Amount" the formula return #Ref!

Regards


Roger Ramseier

 

[Roger Govier]

Hi Roger

You say A3 contains the word "Amount", and yet when you tried to
substitute the word "Amount" in the Getpivotdata function, you used A1

=getpivotdata("Amount",CashFlow!A7,A1,A2)
=getpivotdata(A1,CashFlow!A7,A1,A2)
try

=getpivotdata(A3,CashFlow!A7,A1,A2)

 

[Roger The Rabbit]

Hi Roger Govier

Yes I did.

But looking around other queries I have solved the Problem, by using
the T() function it converts the name "Amount" correctly in some way so
by doing the following it worked

=getpivotdata(T(A1),CashFlow!A7,A1,A2)

Why the A1 and A2 reference did not require it and only the datafield
required to be converted to text using the T() functio I dont know, the
main thing is that it does.


Thanks


Roger Ramseier

 

[Roger Govier]

Hi Roger

I'm glad you have got it working.
However, I am still curious to know how with "Account Type" entered in
cell A1
T(A1) can return "Amount"

 

[Debra Dalgleish]

I'll bet that Roger R. meant to type:

=getpivotdata(T(A3),CashFlow!A7,A1,A2)

I usually concatenate an empty string to the reference, e.g.:

=getpivotdata(A1 & "",CashFlow!A7,A1,A2)

but I don't know why either method is required for the data_field to use
a reference.