Can an 'IF' function be added into a GETPIVOTDATA formula

[crooser100]

Hi there <?XML:NAMESPACE PREFIX = O /><O:P></O:P>
<O:P></O:P>
I'm trying to populate a master front sheet to automatically populate from my Pivot table once refreshed... <O:P></O:P>
<O:P></O:P>
However each week the data changes and some of the fields may disappear. Is there any way to include a string within the GETPIVOTDATA formula to say that ‘IF’ one of the fields doesn't apprear within the pivot, then return a 0?


Current formula:

<O:P></O:P>
<O:P>=GETPIVOTDATA("Exp Time
(mins)",Sheet1!R4C1,"Plant Area","Stillages","Frequency","Shiftly")</O:P>
<O:P></O:P>
<O:P></O:P>
<O:P>The "stillages" part of the formula is the part that may not be available, i.e next week it would be laydown. Therefore if this is the case I would like to return the formula to return a 0 rather than a #REF as it corrupts all other formulas on the master. </O:P>
<O:P></O:P>
<O:P></O:P>
<O:P>Is it possible to do this?</O:P>

MAny thanks
Jo

 

[Andrew Poulsom]

Try:

=IF(ISERROR(GETPIVOTDATA("Exp Time(mins)",Sheet1!R4C1,"Plant Area","Stillages","Frequency","Shiftly")),0,GETPIVOTDATA("Exp Time(mins)",Sheet1!R4C1,"Plant Area","Stillages","Frequency","Shiftly"))
<?XML:NAMESPACE PREFIX = O /><O:P></O:P>
<O:P></O:P>

 

[crooser100]

Thanks that's perfect!