Problems using Excel 97 worksheet in Excel 2002

[plfoley]

I support some users that use an Excel workbooks created
in Excel 97. This worksheet is linked to a number of other
workbooks created in Excel 97 via SUMIF functions. When
the workbook is opened in Excel 97 on any PC with Windows
98 as the OS, the formulas calculate correctly. When the
workbook is opened in Excel 2002 or Excel 2003 on a PC
with Windows XP, the formulas produce #VALUE errors. I
have installed the latest upgrade for Excel 2002, and
looked into any add-ins that are on the Excel 97 machines,
but the formulas are still not calculating correctly.
Because the formulas work correctly in Excel 97, I am
trying to avoid revising the formulas themselves. Any
suggestions would be very helpful.

 

[Harlan Grove]

I support some users that use an Excel workbooks created
in Excel 97. This worksheet is linked to a number of other
workbooks created in Excel 97 via SUMIF functions. When
the workbook is opened in Excel 97 on any PC with Windows
98 as the OS, the formulas calculate correctly. When the
workbook is opened in Excel 2002 or Excel 2003 on a PC
with Windows XP, the formulas produce #VALUE errors. I

If you mean the first (and/or possibly third) arguments to SUMIF are
references to ranges in other files, they don't actually work in XL97. Only
references to *OPEN* files work in SUMIF calls because its first and third
arguments must be range *objects*, and range objects only exist (as far as
Excel is concerned) in open files.

If these files aren't open in either XL97 or XL2002, then what's going on is
Microsoft's failure to ensure backward compatibility of external reference
semantics. You can tell XL97 not to update formulas with external reference
links, and XL97 won't reevaluate those external references even when it
recalc the formulas containing them. It'll used cached values from the last
time you did update the external references. XL2002 on the other hand seems
to work differently. I don't have XL2002, so I'm not going to guess the
specifics of how it doesn't work, but there's been enough written in the
Excel newsgroups to make it plain that XL2002 has the unfortunate tendency
to update external reference links even when told not to do so.

There is a work-around. Use SUMPRODUCT rather than SUMIF. For example,
replace

=SUMIF('C:\foo\[bar.xls]HolySheet'!B2:B25,">0")

with

=SUMPRODUCT(--('C:\foo\[bar.xls]HolySheet'!B2:B25,">0"),
'C:\foo\[bar.xls]HolySheet'!B2:B25)

SUMPRODUCT works without a hitch with references into closed workbooks and
any other arrays you want to throw at it as long as all the array arguments
are the same shape and size.