GETPIVOTDATA #Ref error

[spreadsheet]

Hi,

I have a pivot table scenario where the column and row categories dissappear when filtering by month for example. I understand that part.

However, I am trying to use the 'getpivotdata' command to populate a report using specific cells. This works fine until one of these fields doesn't exist for one month (though it does exist elsewhere in the data table).

Is there a way to alter the getpivotdata formula to return zero instead of an error when an item does not exist at a filtered level.

My long solution was to put the GETPIVOTDATA formula off to the side in another cell, than put an ISERROR formula in the next cell, than put an IF formula in the report cell stating if the ISERROR is TRUE, enter 0, else ref the GETPIVOTDATA cell.

A B C D
1 Sales IF(c1=TRUE,0,=d1) ISERROR(d1) GETPIVOTDATA


There has to be an easier way.

 

[solman]

Why don't you combine ISERROR within the GETPIVOTDATA macro. Hope this works.

=IF(ISERROR(GETPIVOTDATA("xxxx")),0,(GETPIVOTDATA("xxxx"))

 

[spreadsheet]

THANKS!

That works. I was trying to nest it with the IF + ISERROR, but was evidently not getting my logic correct. This was a big help.



I joined yesterday and already got a great answer. Pretty cool.