GETPIVOTDATA Function #REF!

[cmajka]

Hi All -

I can't seem to find the issue with this formula - looking for some help.

=GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5,"POLTYPE",WORK!$E$5,"COMP",WORK!$C$5)

As it stands, the result is a #REF! error. When I step through the calculation steps, i am not getting any errors...suggestions?

Thanks

 

[cmajka]

Actually, the entire formula is here:

=IFERROR(IF(AND(WORK!$C$5="ALL",WORK!$E$5="ALL"),GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5),IF(WORK!$E$5="ALL",GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5,"COMP",WORK!$C$5),IF(WORK!$C$5="ALL",GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5,"POLTYPE",WORK!$E$5),GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5,"POLTYPE",WORK!$E$5,"COMP",WORK!$C$5)))),0)

 

[RoryA]

You'll get a #REF! error if there are no visible cells in the pivot table that match your criteria, or if you have use Custom subtotals on one of the fields.

 

[cmajka]

What do you mean by custom subtotals? I am showing grand totals for each column in the pivot, but that doesn't seem custom to me, it is just part of the pivot table default.

 

[RoryA]

There used to be an issue with GETPIVOTDATA whereby if you changed the field settings to use a Custom subtotal (right-click, Field Settings), GETPIVOTDATA wouldn't work, but it seems to be OK in 2010.

I'd have to guess that one or more of your criteria are incorrect - make sure your criteria cell formats match the format the pivot contains. For example, if you have a number field stored as text in the pivot, you either need to format the cell as text or use ""&A1 as the criteria reference.

 

[cmajka]

It's strange because when I choose 'ALL' in the 'COMP' list box, the formulas calculate correctly, and I get the expected values. When I choose one of the options other than 'ALL' in the 'COMP' list box, I am not getting a calculation. The other list boxes seem to be working ok...it is maddening. Thanks for the input!

 

[RoryA]

That would imply it's the COMP criterion that's wrong. What type of data is it - numeric, text, date?

 

[cmajka]

The values are 01, 05, 25. However, they are stored in the access tables (which are connected to the Pivot) as short text. In the excel list they are stored as text (with a green flag in the corner of the cell). However, when I click on the cell, the format at top in the Excel ribbon says 'General'...

 

[RoryA]

Does:
=GETPIVOTDATA("POLNUMBER",EB_6!$A$1,"STATE",WORK!$G$5,"POLTYPE",WORK!$E$5,"COMP",""&WORK!$C$5)
work?

 

[cmajka]

That gives me a #REF! error that says 'Invalid Cell Reference Error'