GETPIVOTDATA with two slicers

[lite4d]

I have a pivot table that I want to draw data from but I have two different slicers(PLANT, MONTH) that filter the data. Here is my formula:

=GETPIVOTDATA("INV DOC", Calcs!$P$5, "Month", Calcs!Q6, "PLANT", Calcs!Q5)

I keep getting the #REF error and I made sure that all the cells are correct and tried to run it without the slicers in it but still get the error. I have changed the name of the field that I want to draw from and that didn't work.

Any idea as to what I have done wrong? I guarantee that it's something simple.

Thanks as always!

 

[Alex Blakenburg]

You will need to show us the pivot table headings at the very least.
It would also be worth going into a cell, putting in an "=" then pointing to a value you want to return and then show us the formula it generated.

Note: if the slicer filters the pivot in such a way that the formula is going to return nothing then you will get #REF

 

[lite4d]

Here a shots of the pivot table and the cell that i want the data to go along with the slicers that are in place to filter the data.

 

[Umma123]

It sounds like this issue could be related to the way Excel searches for field names in your pivot table. Double-check field names ("Month," "PLANT," and "INV DOC") as they appear in the pivot table and exactly the character statement, including any leading/trailing spaces Sometimes these small differences can for #REF error .

When fixing threads like this, I’ve found it helpful to use a tool like SkySuite AI, which can highlight potential issues and suggest fixes as soon as possible. It’s been a time saver for me with complex formulas like GETPIVOTDATA.

 

[lite4d]

I ended up building more measures and filtering the data in the pivots that way and changing the way the pivots displayed the data. I then just referenced the grand totals of the columns that the measures created. The allowed me to get the totals when more than one plant was chosen or just a single plant. Thanks for the replies!

 

[Alex Blakenburg]

Thanks for letting us know. Glad you were able to get it working.