Making =GETPIVOTDATA Dynamic with an SQL Analysis Cube

[SherylV]

I have a table that fills in automatically from a pivot. The pivot is created from an Access database. It has lookups to the pivot that are dynamic instead of absolute so that the table changes based on what is selected.
From this: =GETPIVOTDATA("Number",$A$23,"Area","A","Priority","1")
to this: =GETPIVOTDATA("Number",$A$23,"Area",$K3,"Priority",L$1)

My pivot now uses an SQL Analysis Cube so the GETPIVOTDATA looks like this
=GETPIVOTDATA("[Measures].[Count]",Pivots!$B$39,"[Dataset].[Area]","[Dataset].[Area].&[A]","[Dataset].[Priority]","[Dataset].[Priority].&[1]")

I've tried all sorts of ways to substitute the cell reference for the absolute value, but all I get is a #Ref error. Does anyone know the correct format for this or if it can be done?

 

[Jerry Sullivan]

Try something like this... (replacing with your cell references)

=GETPIVOTDATA("[Measures].[Count]",Pivots!$B$39,"[Dataset].[Area]","[Dataset].[Area].&[" & $K3 & "]","[Dataset].[Priority]","[Dataset].[Priority].&[" & L$1 & "]")

 

[SherylV]

I still get a REF error

 

[Jerry Sullivan]

Please post the formula you are using that does work with the constants, and the formula you are trying with the cell references that isn't working.

Also, try substituting just one of the cell references at a time. It could be a number formatting issue and it would be helpful to know if the formula works when the first reference is substituted but not the second.

 

[SherylV]

Formula with constants:
=GETPIVOTDATA("[Measures].[Total]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&[Area 1]","[Incidents].[Priority]","[Incidents].[Priority].&[Priority 1]")
Formula with cell references:
=GETPIVOTDATA("[Measures].[Total]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&["&$K3&"]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")

There are two fields in the pivot I fill in - Total and Hours. With Total, replacing only one of the contants gives me a REF error, With Hours, if I replace Priority with a cell reference, I get the number, but not if I replace the Area.
Example that works:
= GETPIVOTDATA("[Measures].[Hours]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&[Area 1]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")
Examples that don't work:
= GETPIVOTDATA("[Measures].[Total]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&[Area 1]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")
= GETPIVOTDATA("[Measures].[Hours]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&["&K$3&"]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")
= GETPIVOTDATA("[Measures].[Hours]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&["&K$3&"]","[Incidents].[Priority]","[Incidents].[Priority].&[Priority 1]")

The constants are text fields and the values they return are number fields.

 

[Jerry Sullivan]

That's interesting. I would have expected the second reference (because it is a number) to more likely be the cause of the error.

So I've got to ask the obvious which you've probably already checked...
Does K$3 have the value "Area 1" instead of "Area1", "Area 1" or some other similar but different value?

Is K$3 a constant or does it have a formula?

Perhaps breaking it down in these steps will help lead to the cause...

Start with this version of your formula that works...
= GETPIVOTDATA("[Measures].[Hours]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&[Area 1]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")

Isolate and concatenate the "Area 1" expression...
= GETPIVOTDATA("[Measures].[Hours]",Pivots!$B$39,"[Incidents].[Area]","[Incidents].[Area].&[" & "Area 1" & "]","[Incidents].[Priority]","[Incidents].[Priority].&["&L$1&"]")

Cut "Area 1" from the formula, type in the reference to K$3 in its place, then Paste "Area 1" from the clipboard into Cell K$3 and remove the surrounding quote marks.

The resulting GetPivotData formula will be the same as the one you already listed as not working, but the value in K$3 might be different that it was previously. If the formula works, then that should point to the problem.