function =IFERROR LOOKUP works in excel 2007 not in excel 2003
 

Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without problems) the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

The latest compatability pack is installed.
Any advice appreciated

function =IFERROR LOOKUP works in excel 2007 not in excel 2003
 

The IFERROR function is not compatible with Excel versions prior to Excel
2007. To reproduce your formulas functionality in prior versions you'd use
the combination of IF and ISERROR.

=IF(ISERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} )),0,LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ))

However, this makes the formula twice as long and when there is no error
condition the LOOKUP has to be performed twice.

=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

But, looking at your formula this isn't necessary. Basically, your formula
is conditioned on a single logical test. You want the same value returned if
the value of C9 is =7 so you can reduce that formula to:

=IF(COUNT('HDU Roster'!C9),IF('HDU Roster'!C9=7,8,0),0)

And it traps *any* errors that might be generated.

--
Biff
Microsoft Excel MVP


"David Ryan" wrote in message
...
Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without problems)
the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

The latest compatability pack is installed.
Any advice appreciated

function =IFERROR LOOKUP works in excel 2007 not in excel 2003
 

Thanks worked a treat

"T. Valko" wrote:

The IFERROR function is not compatible with Excel versions prior to Excel
2007. To reproduce your formulas functionality in prior versions you'd use
the combination of IF and ISERROR.

=IF(ISERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} )),0,LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ))

However, this makes the formula twice as long and when there is no error
condition the LOOKUP has to be performed twice.

=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

But, looking at your formula this isn't necessary. Basically, your formula
is conditioned on a single logical test. You want the same value returned if
the value of C9 is =7 so you can reduce that formula to:

=IF(COUNT('HDU Roster'!C9),IF('HDU Roster'!C9=7,8,0),0)

And it traps *any* errors that might be generated.

--
Biff
Microsoft Excel MVP


"David Ryan" wrote in message
...
Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without problems)
the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

The latest compatability pack is installed.
Any advice appreciated

function =IFERROR LOOKUP works in excel 2007 not in excel 2003
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"David Ryan" wrote in message
...
Thanks worked a treat

"T. Valko" wrote:

The IFERROR function is not compatible with Excel versions prior to Excel
2007. To reproduce your formulas functionality in prior versions you'd
use
the combination of IF and ISERROR.

=IF(ISERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} )),0,LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ))

However, this makes the formula twice as long and when there is no error
condition the LOOKUP has to be performed twice.

=IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

But, looking at your formula this isn't necessary. Basically, your
formula
is conditioned on a single logical test. You want the same value returned
if
the value of C9 is =7 so you can reduce that formula to:

=IF(COUNT('HDU Roster'!C9),IF('HDU Roster'!C9=7,8,0),0)

And it traps *any* errors that might be generated.

--
Biff
Microsoft Excel MVP


"David Ryan" wrote in message
...
Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without
problems)
the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

The latest compatability pack is installed.
Any advice appreciated

function =IFERROR LOOKUP works in excel 2007 not in excel 2003
 

Here's a VB module I used in excel 2003

Go to Tools - Macro -- Visual Basic Editor
Select insert - Module
Paste this:

Function IfError(formula As Variant, show As String)
On Error GoTo ErrorHandler
If IsError(formula) Then
IfError = show
Else
IfError = formula
End If
Exit Function
ErrorHandler:
Resume Next
End Function

Then File Close and return to excel

"David Ryan" wrote:

Hi Folks the function below works in excel 2007 but not in 2003.
=IFERROR(LOOKUP('HDU Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)
The file is saved in 97 - 2003 compatibility mode.
When the spreadsheet is opened in excel 2003 (it opens without problems) the
function gives "#name" error messages and the function is now,
=_xlfn.IFERROR(LOOKUP('HDU
Roster'!$C$9,{7,8,11,1330,1430,2245},{8,8,8,8,8,8} ),0)

The latest compatability pack is installed.
Any advice appreciated