GETPIVOTDATE reference table column

[jake32008]

I am entering a GETPIVOTDATA formula in a table that I would like to refer to the value in another table column

The following formula works for SKU 100007

=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[100007]")

The SKU is what I would like to replace with the value from the [Parent Item] column

The following returns a #REF error

=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[@[Parent Item]]")

Any help is much appreciated!

 

[Jerry Sullivan]

Try....

=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,
"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[[B][COLOR="#0000CD"]" & [@[Parent Item]] & "[/COLOR][/B]]")

 

[jake32008]

You are the man Jerry! Thanks!

 

[cmcreynolds]

I just ran into the same issue, Jerry. What if he were trying to reference a particular cell, say A5? I have tried every variation I can think of ["&[A5]&"] and it keeps giving me a #REF. Any ideas?

Also, can you explain why that fix is what it is?

Is it new with Excel 2013? I used a Power Pivot table - is that it? I'm just curious to know "why" that fixed it

Thank you!

 

[Jerry Sullivan]

If you have a GETPIVOTDATA formula that works with a constant reference and you want to substitute a variable reference, it's good to start by isolating the part of the reference that will change.

If this works...
=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[100007]")

Then this should also work...
=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[" & "100007" & "]")

Once you've tested that this works, your task is to find a variable expression that evaluates to "100007" for the example row and also evaluates to the corresponding value for each relative row that the formula is copied.

In the OP's example, the expression: [@[Parent Item]]
...will accomplish that.

To reference a cell such as A5, the expression: A5
...is all that is needed to replace "1000007" for a relative reference, or $A$5 for an absolute reference.

So the final result might be...
=GETPIVOTDATA("[Measures].[DAILY DEMAND NEXT 8 WEEKS]",'PLANNED ORDERS'!$B$3,"[PlannedOrders].[SKU]","[PlannedOrders].[SKU].&[" & A5 & "]")

 

[cmcreynolds]

Okay - I got 1 out of 2 references to work (see below). The reference to $A5, but for some reason, the reference to $A$3 does not (that's what is causing a #REF! error).

Could it be something to do with formatting of the reference cell vs what's in the original data (in this case, PowerPivot)? Because when I try to manually put back what was in the pivot table to start with, [24] doesn't work, neither does ["24"]...only [2.4E1]


=GETPIVOTDATA("[Measures].[TrueCreatedDateCount]",$B$25,"[dClientInfo1].[ClientID]","[dClientInfo1].[ClientID].&["&$A5&"]","[dCalendar1].[Week]","[dCalendar1].[Week].&["&$A$3&"]")

 

[cmcreynolds]

I apologize for answering my own question, but formatting was exactly it. In Power Pivot, the number was in "Decimal Number" format, and I changed it to "Whole Number" and the references and putting in [24] worked.

So, just fyi - keep your cell references consistent.