Unexplained GETPIVOTDATA #REF error

[paulym666]

This is the simplest situation, but it's giving me a #REF error that I can't explain or fix.

I am not writing or constructing a GETPIVOTDATA formula. I have turned on "Generate GetPivotData" in pivot table options, and then in a blank cell I type "=", just move the cursor to a number in a pivot table and then hit enter. The original cell that was blank now has an automatically generated GETPIVOTDATA formula and that formula should deliver the number in the pivot table that I highlighted. But it doesn't - I immediately get a #REF error.

To be clear - this is what I see immediately after the GETPIVOTDATA formula is generated. I haven't subsequently changed the pivot causing there to be no data in the combination of fields the GETPIVOTDATA is referencing.

Can anyone help me work out how to fix it?

Thanks

 

[paulym666]

Hmm... It may be because I had collapsed fields in the pivot and was referencing a subtotal of those collapsed fields. Not sure why Excel wouldn't allow the GETPIVOT data to call back the subtotal though. Perhaps there's a setting/option I need to change?