Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell_ref in place of an item in get pivot data function
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell_ref in place of an item in get pivot data function
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use cell_ref in place of an item in get pivot data function
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" wrote in message ... 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Mid Function with Pivot Table Calculated Item | Excel Worksheet Functions | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Pivot Table missing data item | Excel Worksheet Functions | |||
My Pivot total just repeats item data | Excel Discussion (Misc queries) |