Pivot Table w/IF Stmt

[Guest]

I've been given an Access database that pulls data from an ODBC source. An
Excel file then links to Access tables. Below is an example of a formula on
an Excel spreadsheet - I am getting #REF errors because, I think, there is no
match in D16 in the pivot table which is because there's no data for either
January or February in the ODBC source. Is there a way to combine an IF
statement with the formula below to put a 0 in the cell so it doesn't error
off? Any other ideas? Any help would be so very much appreciated!! Jani
=GETPIVOTDATA("Recordable1",'Recordable
-CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$4,"Division",D16)

 

[Guest]

as a worst case try

if(iserror(=GETPIVOTDATA("Recordable1",'Recordable
-CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",'Recordable
-CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$4,"Division",D16))

 

[Guest]

Thanks, Tom... but didn't work. I put an = sign in front of IF and took it
out before the GETs. Any other thoughts?

 

[Guest]

NO - You were correct! I didn't delete all the old code. Thanks!!! Jani

 

[Guest]

Tom - Can you tell I'm in way to much of a hurry to get this done? There is a
problem in that the formula always enters a 0. It is not picking up if there
is data. Thoughts??? Jani

"=IF(ISERROR(GETPIVOTDATA(""Recordable1"",'Recordable
-CY'!$A$204,""Region"",C16,""Location"",B16,""Month_"",E$4,""Division"",D16)),0,GETPIVOTDATA(""Recordable1"",'Recordable
-CY'!$A$204,""Region"",C16,""Location"",B16,""Month_"",E$4,""Division"",D16))"

 

[Guest]

If that doesn't work (and it may not - I never use GetPivotData - it is too
verbose for me)

Possibly you can use countif to see if the data you are looking for is
there. so as a general approach,

=if(sum(month(B1:B300)=1)=0,0,Getpivotformula)

entered with Ctrl+shift+Enter

in the =1, replace with Month(cell that contains a date with the month)

You know how your data is laid out and what you are looking for, so adapt
the approach to your specifics.

 

[Guest]

I'll give it a try, Tom. Thank you!

If that doesn't work (and it may not - I never use GetPivotData - it is too
verbose for me)

Possibly you can use countif to see if the data you are looking for is
there. so as a general approach,

=if(sum(month(B1:B300)=1)=0,0,Getpivotformula)

entered with Ctrl+shift+Enter

in the =1, replace with Month(cell that contains a date with the month)

You know how your data is laid out and what you are looking for, so adapt
the approach to your specifics.