Prev: pop up calender
Next: Excel2007 Excel Help "This Page is Unavailable"
From: Jamil on 28 Apr 2010 11:36
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?
From: "Bernie Deitrick" deitbe on 28 Apr 2010 14:40
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" <Jamil(a)discussions.microsoft.com> wrote in message
news:47A5CBB2-E4B2-46AE-AB1E-54FBCEA46611(a)microsoft.com...
>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?


From: Jamil on 28 Apr 2010 16:28
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" wrote:

> 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" <Jamil(a)discussions.microsoft.com> wrote in message
> news:47A5CBB2-E4B2-46AE-AB1E-54FBCEA46611(a)microsoft.com...
> >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?
>
>
> .
>
 | 
Pages: 1
Prev: pop up calender
Next: Excel2007 Excel Help "This Page is Unavailable"