Getpivotdata function Excel 2002 1

[TGHaddon]

I have a problem with the use of the getpivotdata function in a spreadsheet I've just created.

=GETPIVOTDATA("Sum of DOZENS",Pivot!$A$3,"REP",B11,"SUPPLIER",$A$6)

For the most part it's working fine, using the cell references to retrieve the data I need on another sheet. However I've now run into some instances where the cells return #REF! results as the information they contain doesn't feature in the original data, but may do in the future.

Is there a simple way of changing the formula so that it returns a 0 result if the "Rep" or "Supplier" field isn't present in the pivot table rather than an error message?

Sorry not to be clearer but I'm finding it hard to describe what I'm after.

Tim

 

[xlbo]

wrap in an IF statement

=IF(ISERROR(GETPIVOTDATA("Sum of DOZENS",Pivot!$A$3,"REP",B11,"SUPPLIER",$A$6)),0,GETPIVOTDATA("Sum of DOZENS",Pivot!$A$3,"REP",B11,"SUPPLIER",$A$6))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TGHaddon]

Excellent, sorted now, I was playing with IF statements but not being aware of the ISERROR function couldn't get them to work.

Cheers

Tim