#ref

  • Thread starter Thread starter jengy1
  • Start date Start date
J

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