| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table w/IF Stmt
I've been given an Access database that pulls data from an ODBC source. An
Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
as a worst case try
if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
Thanks, Tom... but didn't work. I put an = sign in front of IF and took it
out before the GETs. Any other thoughts? "Tom Ogilvy" wrote: as a worst case try if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
NO - You were correct! I didn't delete all the old code. Thanks!!! Jani
"Tom Ogilvy" wrote: as a worst case try if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
Tom - Can you tell I'm in way to much of a hurry to get this done? There is a
problem in that the formula always enters a 0. It is not picking up if there is data. Thoughts??? Jani "=IF(ISERROR(GETPIVOTDATA(""Recordable1"",'Recorda ble -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16)),0,GETPIVOTDATA(""Record able1"",'Recordable -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16))" "Tom Ogilvy" wrote: as a worst case try if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
If that doesn't work (and it may not - I never use GetPivotData - it is too
verbose for me) Possibly you can use countif to see if the data you are looking for is there. so as a general approach, =if(sum(month(B1:B300)=1)=0,0,Getpivotformula) entered with Ctrl+shift+Enter in the =1, replace with Month(cell that contains a date with the month) You know how your data is laid out and what you are looking for, so adapt the approach to your specifics. -- regards, Tom Ogilvy "Jani" wrote: Tom - Can you tell I'm in way to much of a hurry to get this done? There is a problem in that the formula always enters a 0. It is not picking up if there is data. Thoughts??? Jani "=IF(ISERROR(GETPIVOTDATA(""Recordable1"",'Recorda ble -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16)),0,GETPIVOTDATA(""Record able1"",'Recordable -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16))" "Tom Ogilvy" wrote: as a worst case try if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table w/IF Stmt
I'll give it a try, Tom. Thank you!
"Tom Ogilvy" wrote: If that doesn't work (and it may not - I never use GetPivotData - it is too verbose for me) Possibly you can use countif to see if the data you are looking for is there. so as a general approach, =if(sum(month(B1:B300)=1)=0,0,Getpivotformula) entered with Ctrl+shift+Enter in the =1, replace with Month(cell that contains a date with the month) You know how your data is laid out and what you are looking for, so adapt the approach to your specifics. -- regards, Tom Ogilvy "Jani" wrote: Tom - Can you tell I'm in way to much of a hurry to get this done? There is a problem in that the formula always enters a 0. It is not picking up if there is data. Thoughts??? Jani "=IF(ISERROR(GETPIVOTDATA(""Recordable1"",'Recorda ble -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16)),0,GETPIVOTDATA(""Record able1"",'Recordable -CY'!$A$204,""Region"",C16,""Location"",B16,""Month _"",E$4,""Division"",D16))" "Tom Ogilvy" wrote: as a worst case try if(iserror(=GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)),0,=GETPIVOTDATA("Recordable1",' Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16)) -- Regards, Tom Ogilvy "Jani" wrote: I've been given an Access database that pulls data from an ODBC source. An Excel file then links to Access tables. Below is an example of a formula on an Excel spreadsheet - I am getting #REF errors because, I think, there is no match in D16 in the pivot table which is because there's no data for either January or February in the ODBC source. Is there a way to combine an IF statement with the formula below to put a 0 in the cell so it doesn't error off? Any other ideas? Any help would be so very much appreciated!! Jani =GETPIVOTDATA("Recordable1",'Recordable -CY'!$A$204,"Region",C16,"Location",B16,"Month_",E$ 4,"Division",D16) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Print Pivot Table headers on pages with Pivot Table | Excel Worksheet Functions | |||
| Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
| Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
| Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming | |||
Hybrid Mode
