Unable to get data from Pivot Table via GETPIVOTDATA

SampleGetPivotDataV2.png

I am a new user so hope this message contains enough info.

The above Pivot Table is generated from data imported into a model via and ODBC.Query.

I have tried numerous variations of the GETPIVOTDATA formula, both via Excel as well as VBA and all I get is the dreaded #REF!

Variations included:
* using table name vs range
* using Item values vs values from a cell
* using name of the data field as "Sum of MovementMTD" vs "MovementMTD"
* changing pivot table Row Labels and Column Labels to name of the data fields

The following are some of the variations I have tried:
=GETPIVOTDATA("Sum of MovementMTD","PivotTable1","GLAccountCode","555.5057.5","PeriodNo","2")
=GETPIVOTDATA("Sum of MovementMTD",$B$3:$O$21,"GLAccountCode","555.5057.5","PeriodNo","2")
=GETPIVOTDATA("Sum of MovementMTD",$B$3,"GLAccountCode",$Q$13,"PeriodNo",$Q$16)

What we are trying to achieve is to update other sheets in the workbook by extracting the values from this imported pivot table based on known GLAccountCode and PeriodNo.

Really would appreciate assistance with this and suggestions on what else I am able to try.

Thanks

Using the GETPIVOTDATA formula to extract a value from a Pivot Table that is based on a data model is WAY different to a normal Pivot table.

The following are equivalent formula for a data model pivot table:

=GETPIVOTDATA("Sum of MovementMTD",$B$3:$O$21,"GLAccountCode","555.5057.5","PeriodNo","2")
Should be:
=GETPIVOTDATA("[Measures].[Sum of MovementMTD]",$B$3,"[GLBalances].[GLAccountCode]","[GLBalances].[GLAccountCode].&[555.5057.5]","[GLBalances].
[PeriodNo]","[GLBalances].[PeriodNo].&[2]")

=GETPIVOTDATA("Sum of MovementMTD",$B$3,"GLAccountCode",$Q$13,"PeriodNo",$Q$16)
Should be:
=GETPIVOTDATA("[Measures].[Sum of MovementMTD]",$B$3,"[GLBalances].[GLAccountCode]","[GLBalances].[GLAccountCode].&[" & $Q$13 & "]","[GLBalances].
[PeriodNo]","[GLBalances].[PeriodNo].&[" & $Q$16 & "]")

Hope the above is helpful as I really spent a lot of frustration trying to get this to work.