=GETPIVOTDATA & Expanding Pivot Table

[Jimbo123456]

So I have discovered that =GETPIVOTDATA is only able to lookup data that is expanded and visible. Ie. If I want January's Sales figure but the whole year is grouped/condensed it will give a Ref error until you hit the button to expand the year group.

Is there really no way to point to data that is "hidden" inside of the Pivot table? I'm especially concerned that if someone changes the pivot table formatting (ie. closes a year from showing months to year level) it will break many formulas.

[Olly]

Load your data to the Data Model, then you can use CUBEVALUE formulae to retrieve specific values.