Excel 2007 vs 2003 Formula problem

[waynen]

Can you point me in the right direction to try and resolve the following
issue please?

Our engineers use an Excel file to track project progress and we need to
submit this weekly to the client for review.
One of the issues we found was that the subtotals from one version to the
next would differ in value and one of the reasons we found was with the
ISERROR difference between 2007 & 2003, but we did not go in depth to
identify all the issues between the two versions of excel.
Imagine having to doubt the calculation every week when you submit the file
to the client.
The problem does not occur when opening the file in Excel 2007 but when
trying to save when already in 2007 to a 2003 version.

They have requested Excel 2003 to be installed as well as 2007 so that they
can work in whicever one they need for a specific file. This seems to be a
poor solution to me, and I am convinced there must be a better fix.

One solution I can think of is to get the engineer to save in 2003 format,
vet the file for any formula errors, fix them, and then keep it in 2003
format from then on. The file and worksheets are very big though, and they
will argue that they shouldn't have to do this (which I agree with).

 

[T. Valko]

You need to post the formula that's causing problems.

the ISERROR difference between 2007 & 2003

There is no difference in that function between versions. Perhaps you're
thinking of the Excel 2007 function IFERROR compared to the ISERROR
function. Note that these are entirely different functions. ISERROR is
compatible with all versions of Excel while IFERROR only works in Excel
2007.

 

[waynen]

Thanks Biff,
What we have seen is that the formula"=SUMIF('Progress
Tracking'!$K$9:$K$312,'BOQ Progress'!A10,'Progress Tracking'!$DM$9:$DM$312)"
in the column "L" in BOQ Progress worksheet does not return correct data.
Upon using the Compatibility Checker, a 'Minor Loss of Fidelity' dialog box
appears stating "Some formulas in this workbook are linked to other workbooks
that are closed. When these formulas are recalculated in earlier versions of
Excel without opening the linked workbooks, characters beyond the
255-character limit cannot be returned. Location: Defined Names"
Number of Occurrences is 27.
Wayne



--
Wayne
Virtual Roamer

You need to post the formula that's causing problems.

the ISERROR difference between 2007 & 2003

There is no difference in that function between versions. Perhaps you're
thinking of the Excel 2007 function IFERROR compared to the ISERROR
function. Note that these are entirely different functions. ISERROR is
compatible with all versions of Excel while IFERROR only works in Excel
2007.

 

[T. Valko]

The SUMIF function doesn't work properly (in any version of Excel) unless
the source file is open.

The formula you posted doesn't link to an external file so I don't know what
that warning message means *unless* you have other similar formula that do
link to an external file.

--
Biff
Microsoft Excel MVP

Thanks Biff,
What we have seen is that the formula"=SUMIF('Progress
Tracking'!$K$9:$K$312,'BOQ Progress'!A10,'Progress
Tracking'!$DM$9:$DM$312)"
in the column "L" in BOQ Progress worksheet does not return correct data.
Upon using the Compatibility Checker, a 'Minor Loss of Fidelity' dialog
box
appears stating "Some formulas in this workbook are linked to other
workbooks
that are closed. When these formulas are recalculated in earlier versions
of
Excel without opening the linked workbooks, characters beyond the
255-character limit cannot be returned. Location: Defined Names"
Number of Occurrences is 27.
Wayne