Excel 2003/2007 not displaying results of Excel 2002 formula

[rhinohelix]

At my company we recently began converting from Office 2002/XP to Office 2003. An issue has arisen where a spreadsheet involving pivots tables will display results in 2002 but are empty when viewed in Excel 2003 or 2007. Here is the formula in question:

=IF(ISERR(GETPIVOTDATA("DRESS",'{file location calling another spreadsheet[Filename.xls]}XXXXX Pivot Table'!$A$117,"Month",D$1,"District",$A$2,"Wage Level",$B3)),0,GETPIVOTDATA("DRESS",'{file location calling another spreadsheet[Filename.xls]}XXXX Pivot Table'!$A$117,"Month",D$1,"District",$A$2,"Wage Level",$B3))

I don't post it to figure out if there is something wrong with it, as none of the Excel versions have an issue with the formula but to determine why of the 3 machines I have in front of me only the one with Excel 2002 displays the results while the ones with Excel 2003 and 2007 don't. Any help, even a hint of the direction in which to look would be appreciated.

Edited April 3, 2007 by rhinohelix

[jaclaz]

First thing I would do is to remove the IF(ISERR check, with that enabled, ANY error will return value 0, without it, it should show some error, like N/A or NAME?:

change from

=IF(ISERR(GETPIVOTDATA("DRESS",'{file location calling another spreadsheet[Filename.xls]}XXXXX Pivot Table'!$A$117,"Month",D$1,"District",$A$2,"Wage Level",$B3)),0,GETPIVOTDATA("DRESS",'{file location calling another spreadsheet[Filename.xls]}XXXX Pivot Table'!$A$117,"Month",D$1,"District",$A$2,"Wage Level",$B3))

to

=GETPIVOTDATA("DRESS",'{file location calling another spreadsheet[Filename.xls]}XXXX Pivot Table'!$A$117,"Month",D$1,"District",$A$2,"Wage Level",$B3)

jaclaz

[rhinohelix]

Is this what I am looking for as to why it displays in 2002 and doesn't display anything in the later versions, specifically 2003? What changed between versions that would cause it not to display? Was there something the way 2002 and 2003 displays the errors, etc.? Other than changes to the Analysis Tool Pack to some of the functions, I can't find any changes between the versions that would cause this.

In the 2002 version, a number is displayed there, which allows the rest of the form to be populated, as the other formula and cells draw off it. In the 2003 version, nothing is displayed there, which messes up the rest of the spreadsheet. My quandry is what would the difference be, and how can we get the 2003 version to get the same results as 2002? Right now there are members of this group demanding to be rolled back to 2002, which is something I am desperately trying to avoid.

Sorry for the vagueness of my post and TIA for your assistance.

[jaclaz]

As said, I have no idea about WHY it happens, all I can do is to suggest a procedure (HOW) it would be possible to (hopefully) track down the problem.

Basically that conditional IF(ISERR(FUNCTION),0,(RESULT)) will show 0, possibly a blank cell if EXCEL is set not to show 0 values, WHATEVER error the FUNCTION actually gives as result.

Such an error can be derived by a number of causes, knowing the type of error might help.

It is possible that the problem is due to some differences in the general settings of XL2003 when compared to XL2002, something in Calculation Tab, maybe.

jaclaz

Edited April 4, 2007 by jaclaz