Using Cell Reference in GETPIVOTDATA

[BiggusDoggus]

Hi all

I have scoured Google (including this site) and can't find the answer to this - there have been solutions given to other people that just don't work for me.

I have this formula:

=GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&[Mar]","[Range 1].[Region]","[Range 1].[Region].&[Bay of Plenty]")/GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&[Mar]")

Now, what I'm wanting to be able to do is replace the values in bold with cell references. For example, "Mar" to be replaced with E2, and "Bay of Plenty" with B4. The table with the formulas is going to be sizeable, and likely to have dynamic values in the Region (column B). And it's sizeable - I really don't want to have to go in and manually change the Region if it varies!

Any help greatly appreciated thanks!

 

[Rickmaurinus]

Hi !

The following should do the trick:

=GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&[" & A2 &"]","[Range 1].[Region]","[Range 1].[Region].&["& B2 &"]")/GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&["& A2 & "]")

Make sure to start with quotation " , then use & sign, then add a cell reference and then & sign again, and end with quotation " again.

Let me know if it works! Good luck!

Rick

 

[BiggusDoggus]

Hi Rick, thanks for that.

But unfortunately no, getting #REF . Just for validation, here is what I've used:
=GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&[" & A2 &"]","[Range 1].[Region]","[Range 1].[Region].&[" & B2 &"]")/GETPIVOTDATA("[Measures].[Sum of Actuals]",'2017 Calc'!$A$3,"[Range 1].[Order Date (Month)]","[Range 1].[Order Date (Month)].&[" & A2 &"]")

 

[BiggusDoggus]

I've worked around the issue by changing the setting so that Excel stops using the GETPIVOTDATA functions, and am using INDEX MATCH instead. Thanks for your help though, Rick!

 

[Rickmaurinus]

He Biggus,

Index Match is definitely a working alternative.

If you want to continue the GETPIVOTDATA formula anyway I usually track the formula error as follows:
First I select the cell in the pivot I need. Excel will create the GETPIVOTDATA formula automatically. Then I change the first part of the formula that I want to make dynamic. If that works, the second part etc.

One source of problems could be referring to a date. You may want to refer to the month March, and link to a cell containing a date, formatted as month. In reality, the GETPIVOTDATA may not ask for a DATE field, but a TEXT field with "Mar". Changing the formatting of the cell I make a reference to from date to TEXT then sometimes is the solution.

Anyway, Great you got it working. If you need anymore help you can always send me an excel file and I'll have a look at it.

Cheers,
Rick

 

[BiggusDoggus]

I appreciate the advice Rick - thank you.