How to use a formula in Getpivotdata data fields

[perplexedphilip]

I am trying to use the getpivotdata function in excel. I can use it to simply drag data from a pivot table in another file quite easily as below:

=GETPIVOTDATA("Temperature",'[20090109.xls]Sheet1'!$B4,"Date",DATE(2009,1,9),"Hour","00")

I would like to be able to make this formula more intelligent, rather than having to specify that I want data from the field "Temperature", I would like the function to use the data field specified in a separate cell. To look something like

=GETPIVOTDATA(=(B1),'[20090109.xls]Sheet1'!$B4,"Date",DATE(2009,1,9),"Hour","00")

Where B1 contains "Temperature"

I have a tried a few different things but cannot get this to work. I was hoping that someone may be able to offer advice as to how to use a simple formula in a data field of another formula.

Any help would be appreciated.

 

[nkiouari]

Hi,
I don't know if it will help, but I just tried in my french version of excel, as I'm in a francophone country, with drop down and it worked fine with the formula T(value).

Normally your formula should look like :
=GETPIVOTDATA(T(B1),'[20090109.xls]Sheet1'!$B4,"Date",DATE(2009,1,9),"Hour","00")

where T(value) cheks if a value is pointing to a text and return that text; and in B1 you should customize a dropdown menu according to your need.

Tell me if it works,

 

[Gabriel222]

Worked for me !
Merci Nkiouari