Prev: What's this: [Sales] ?
Next: Text from number
From: jengy1 on 4 Mar 2010 06:00
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")
From: Bob Phillips on 4 Mar 2010 07:31
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" <jengy1(a)discussions.microsoft.com> wrote in message
news:44ECB622-77D5-4BD1-8FDF-DA12AE440E5D(a)microsoft.com...
> 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")


 | 
Pages: 1
Prev: What's this: [Sales] ?
Next: Text from number