Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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







  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,814
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IFERROR in Excel 2007 Dirrk Excel Worksheet Functions 5 April 25th 10 10:08 PM
Lookup Function for Excel 2007 Cornelius Excel Discussion (Misc queries) 2 March 6th 09 01:44 AM
Excel 2003 Lookup function Newfie809 Excel Worksheet Functions 3 November 12th 07 07:06 PM
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 gromit12 Excel Discussion (Misc queries) 2 November 6th 07 10:30 PM
Visual Basic Function works fine in Excell 2003 but not in Excel 2 Roger Excel Discussion (Misc queries) 8 August 1st 07 03:56 AM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"