#ref

[jengy1]

iam currently using the below formula, if there is no data for a month i am
getting the #ref
i have tried adding ifserror to input a 0 if necesary but unfortunately have
no joy, any suggestions
=GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY")-GETPIVOTDATA("TOTAL ASSIGNMENTS
COLLECTED",'PIVOT DATA'!$A$54,"month","FEBRUARY","less than 21","NOT
RETURNED")

 

[Bob Phillips]

This worked for me

=IF(ISERROR(GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY")
-GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY","less than 21","NOT RETURNED")),0,
GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY")
-GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY","less than 21","NOT RETURNED"))

or if you have Excel 2007

=IFERROR(GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY")
-GETPIVOTDATA("TOTAL ASSIGNMENTS COLLECTED",'PIVOT
DATA'!$A$54,"month","FEBRUARY","less than 21","NOT RETURNED"),0)