# GETPIVOTDATA Function #REF!

#### cmajka

##### Board Regular
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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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)

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.

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.

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.

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!

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

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'...

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

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

Replies
15
Views
1K
Replies
1
Views
2K
Replies
6
Views
119
Replies
5
Views
85
Replies
1
Views
152

1,196,101
Messages
6,013,459
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back