Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IFERROR in Excel 2007 | Excel Worksheet Functions | |||
Lookup Function for Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2003 Lookup function | Excel Worksheet Functions | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) |