|
Getpivotdata and #REF
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!! |
Getpivotdata and #REF
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" wrote: 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!! |
Getpivotdata and #REF
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" wrote: 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" wrote: 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!! |
Getpivotdata and #REF
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" wrote: 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" wrote: 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" wrote: 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!! |
Getpivotdata and #REF
it worked :) Thank you tons!!!
"Jim Thomlinson" wrote: 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" wrote: 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" wrote: 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" wrote: 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!! |
Getpivotdata and #REF
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. Bonnie wrote: 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!! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 08:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com