GetPivotDate returns #REF w/o empty quotes at end

[sherwole]

Hi All,
I am using the same formula to get data from one pivot table:

=GETPIVOTDATA("Sum of No. Reqd",'OCC90 PivT Level 3 Forms'!$A$3,"Sub-System",B234,"Disp.","OVERALL","Week #","")

this is the formula that excel automatically wrote when I clicked on the cell I wanted to read data from in the pivot table. When I copied it down the column, it worked for almost all the cells, however, some returned a reference error. I cannot find a pattern for those that are returning #REF, but if I delete the empty quotes at the end of the reference string so that the formula reads:

=GETPIVOTDATA("Sum of No. Reqd",'OCC90 PivT Level 3 Forms'!$A$3,"Sub-System",B423,"Disp.","OVERALL","Week #",)

the formula returns the correct value. What's the deal?!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

The syntax of GETPIVOTDATA is:
GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)

So in your first example:
data_field = "Sum of No. Reqd"
pivot_table = 'OCC90 PivT Level 3 Forms'!$A$3 (this is the address of the PivotTable)

[field1, item1] = "Sub-System", B234
[field2, item2] = "Disp.","OVERALL"
[field3, item3] = "Week #", ""

So for the formulas that work without error, there is Visible Item in the PivotTable for the combination of field1,2,3 items.

For the ones that return #REF!, it means there isn't a Visible Item for that combination.

I'm not positive how Excel interprets the formula when the Field parameter is listed but there is no item after the comma as in:
..."Disp.","OVERALL","Week #",)

The fact that this worked for all your formulas makes me think Excel treats this as a wildcard that matches any items in field "Week #".