#ref
 

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")

#ref
 

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)

--

HTH

Bob

"jengy1" wrote in message
...
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")