Use cell_ref in place of an item in get pivot data function


J

Jamil

I want to use the getpivotdata function to get data based on a cell reference
in excel 2007. My pivot table is from an analysis cube.
The getpivotdata function that is auto generated from clicking on the data I
want is
=GETPIVOTDATA("[Measures].[Order Quantity]",$B$5,"[Order Date Dim].[Year
Number]","[Order Date Dim].[Year Number].&[2010]","[Order Date Dim].[Week
Number Of Year]","[Order Date Dim].[Week Number Of
Year].&[17]","[Product].[SKU]","[Product].[SKU].&[2]")

I want to change the SKU that is retrieved from [2] (which appears to be a
reference to the 2nd record for SKU) to a cell reference. This way I could
take this formula and use it to retreive the same data for whatever SKU is in
the referenced cell. I hope my question makes sense?
 
Ad

Advertisements

B

Bernie Deitrick

Changing the part after the last comma to a cell reference is how it is done
in XL 2003, at least:

Try

=GETPIVOTDATA("[Measures].[Order Quantity]",$B$5,"[Order Date Dim].[Year
Number]","[Order Date Dim].[Year Number].&[2010]","[Order Date Dim].[Week
Number Of Year]","[Order Date Dim].[Week Number Of
Year].&[17]","[Product].[SKU]",B2)

and enter a valid SKU into cell B2.

HTH,
Bernie
MS Excel MVP
 
Ad

Advertisements

J

Jamil

Just returned #ref.

Somehow I still need to pair the field with the item, which in this case the
field being [Product].[SKU], and the actual SKU being the item. In the get
pivot data function auto generated from the pivot table the item is simply
[2], which I think is a reference to the 2nd record or item in SKU.

Bernie Deitrick said:
Changing the part after the last comma to a cell reference is how it is done
in XL 2003, at least:

Try

=GETPIVOTDATA("[Measures].[Order Quantity]",$B$5,"[Order Date Dim].[Year
Number]","[Order Date Dim].[Year Number].&[2010]","[Order Date Dim].[Week
Number Of Year]","[Order Date Dim].[Week Number Of
Year].&[17]","[Product].[SKU]",B2)

and enter a valid SKU into cell B2.

HTH,
Bernie
MS Excel MVP


Jamil said:
I want to use the getpivotdata function to get data based on a cell
reference
in excel 2007. My pivot table is from an analysis cube.
The getpivotdata function that is auto generated from clicking on the data
I
want is
=GETPIVOTDATA("[Measures].[Order Quantity]",$B$5,"[Order Date Dim].[Year
Number]","[Order Date Dim].[Year Number].&[2010]","[Order Date Dim].[Week
Number Of Year]","[Order Date Dim].[Week Number Of
Year].&[17]","[Product].[SKU]","[Product].[SKU].&[2]")

I want to change the SKU that is retrieved from [2] (which appears to be a
reference to the 2nd record for SKU) to a cell reference. This way I
could
take this formula and use it to retreive the same data for whatever SKU is
in
the referenced cell. I hope my question makes sense?


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top