Getpivotdata and #REF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to return a zero value if the result of my getpivotdata formula is
going to return #REF. How do I do this?

Here is the formula:
=+GETPIVOTDATA("USD TOTAL",'[0407 COMBO Pivots.xls]OEM'!$A$3,"Region per
L/U","CAN")

Also.... Is there a way to pull the getpivotdata results onto my report when
the external workbook that it is pulling from is not opened? I've tried
adding the filepath name to the formula string, but that only returns
#REF....please help!!
 
Try this...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN"), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))
 
i tried it and i get an error message pointing to the , 0,
I tried taking out the space and that didn't do anything. Is there anything
that I'm missing?

Jim Thomlinson said:
Try this...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN"), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))
--
HTH...

Jim Thomlinson


Bonnie said:
I want to return a zero value if the result of my getpivotdata formula is
going to return #REF. How do I do this?

Here is the formula:
=+GETPIVOTDATA("USD TOTAL",'[0407 COMBO Pivots.xls]OEM'!$A$3,"Region per
L/U","CAN")

Also.... Is there a way to pull the getpivotdata results onto my report when
the external workbook that it is pulling from is not opened? I've tried
adding the filepath name to the formula string, but that only returns
#REF....please help!!
Click to expand...
Click to expand...
 
Sorry I missed a bracket...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN")), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))

--
HTH...

Jim Thomlinson


Bonnie said:
i tried it and i get an error message pointing to the , 0,
I tried taking out the space and that didn't do anything. Is there anything
that I'm missing?

Jim Thomlinson said:
Try this...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN"), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))
--
HTH...

Jim Thomlinson


Bonnie said:
I want to return a zero value if the result of my getpivotdata formula is
going to return #REF. How do I do this?

Here is the formula:
=+GETPIVOTDATA("USD TOTAL",'[0407 COMBO Pivots.xls]OEM'!$A$3,"Region per
L/U","CAN")

Also.... Is there a way to pull the getpivotdata results onto my report when
the external workbook that it is pulling from is not opened? I've tried
adding the filepath name to the formula string, but that only returns
#REF....please help!!
Click to expand...
Click to expand...
Click to expand...
 
it worked :) Thank you tons!!!

Jim Thomlinson said:
Sorry I missed a bracket...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN")), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))

--
HTH...

Jim Thomlinson


Bonnie said:
i tried it and i get an error message pointing to the , 0,
I tried taking out the space and that didn't do anything. Is there anything
that I'm missing?

Jim Thomlinson said:
Try this...

=if(iserror(GETPIVOTDATA("USD TOTAL",'[0407 COMBO
Pivots.xls]OEM'!A$3,"Region per L/U","CAN"), 0, GETPIVOTDATA("USD
TOTAL",'[0407 COMBO Pivots.xls]OEM'!A$3,"Region per L/U","CAN"))
--
HTH...

Jim Thomlinson


:

I want to return a zero value if the result of my getpivotdata formula is
going to return #REF. How do I do this?

Here is the formula:
=+GETPIVOTDATA("USD TOTAL",'[0407 COMBO Pivots.xls]OEM'!$A$3,"Region per
L/U","CAN")

Also.... Is there a way to pull the getpivotdata results onto my report when
the external workbook that it is pulling from is not opened? I've tried
adding the filepath name to the formula string, but that only returns
#REF....please help!!
Click to expand...
Click to expand...
Click to expand...
 
For your second question, no, the other workbook must be open if you
refer to it in a GetPivotData formula.

Perhaps you could create the GetPivotData formulas on a hidden sheet in
the other workbook, and link to those cells instead.
I want to return a zero value if the result of my getpivotdata formula is
going to return #REF. How do I do this?

Here is the formula:
=+GETPIVOTDATA("USD TOTAL",'[0407 COMBO Pivots.xls]OEM'!$A$3,"Region per
L/U","CAN")

Also.... Is there a way to pull the getpivotdata results onto my report when
the external workbook that it is pulling from is not opened? I've tried
adding the filepath name to the formula string, but that only returns
#REF....please help!!
Click to expand...
 
Back
Top