get pivot data - if no data return <blank> instead of #ref!

 Hello,

I have a workbook set up with a few different pivot tables. On one sheet I am matching the data from 4 different pivots trough the "get pivot data" command.

 A simple example:

=GETPIVOTDATA("data";'sheet'!$A$3;"income";"week 20")-GETPIVOTDATA("data";'sheet'!$A$3;"expense";"week 21")

if there is no expense week 21 data at the moment, the command will return "#ref!" as it cant find the desired pivot field - I want it to return "0" instead. Help is greatly appreciated!

Thanks,

Christian

 Make sure the pivot table is referencing the source data properly; if not have the pivot table point to the proper source database, and it should correct.

sorry, i must have been a bit unclear.

at present the source data is not complete (will be added as the year go along). My source data is divided in several data sets (sheets), i am using 4 pivots and the GETPIVOTDATA command to display the information from my 4 pivot tables in the way i want.

what I am after is an if/error command (or the like) which will return "0" or nothing instead of "#REF!" from a GETPIVOTDATA command that is unable to find its reference.

Ill try not to make it more confusing, but here's an explanation of what I am trying to do.

ie, in my output sheet, one cell has 4 getpivotdata commands. If one of those commands are referring to a field which has not been added to the pivottable (as the source data is not complete), the cell displays "#ref!" - I want the cell to display the result of the other three getpivotdata commands, disregarding from the "faulty" getpivotdata command.

Hope that clarifies,

Best regards,

Christian

 I don't really know much, but what I think will work is this:

IF(

=GETPIVOTDATA("data";'sheet'!$A$3;"income";"week 20")-GETPIVOTDATA("data";'sheet'!$A$3;"expense";"week 21")<>0,

=GETPIVOTDATA("data";'sheet'!$A$3;"income";"week 20")-GETPIVOTDATA("data";'sheet'!$A$3;"expense";"week 21"), "")

Saying, if it isn't eq to zero (or blank ""), GETPIVOTDATE, else print 0 or "" (blank)

You could probably also set a conditional format wherein IF result eq #REF then print 0 or ""

well, the problem is that the pivot field reference can not be found, which makes the getpivotdata return "#REF!".

Each cell has four get pivot data commands - one or two of them might return "#ref!" as its reference is not available - thus excel would not accept above formula as it would end up too long i suppose.

I remember hearing something about an if/error command or such which would make a formula return blank instead of "#ref!" if there were something wrong in it, but I cant seem to find any info about it now...

Any help is very appreciated,

Christian

 You can wrap each section in an IF formula:

=IF(ISERROR(GETPIVOTDATA("data";'sheet'!$A$3;"income";"week 20"));0;GETPIVOTDATA("data";'sheet'!$A$3;"income";"week 20"))-IF(ISERROR(GETPIVOTDATA("data";'sheet'!$A$3;"expense";"week 21"));0;GETPIVOTDATA("data";'sheet'!$A$3;"expense";"week 21"))

 Yes, 

The  ISERROR  is the correct way to check for the return code as  GETPIVOTDATA does not actually return  #REF!

I had the exact same problem.  I update my pivots each week,and in the case where there was no data for that week,  my pivotlookup  would return #ref!   and break the chart data.

Thanks for that.

Gar

Oh, forgot to reply to debras post. Debra, thanks - the iserror command works fine. The code gets somewhat long, but it does the trick.

/christian