+ Reply to Thread
Results 1 to 11 of 11

Formula compatability

  1. #1
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Formula compatability

    Hi.

    I have a problem with which i hope someone can help.

    I have a spreadsheet created in Excel 2011, the issue I have is I need to use it on Excel 2003. In the spreadsheet i have the following formula:

    =IF(ISBLANK($A3),"",IFERROR(INDEX(Temporary!$A$5:$N$752,MATCH(A3,Temporary!$A$5:$A$752,0),14),IFERROR(INDEX(Permanent!$A$5:$N$752,MATCH(A3,Permanent!$A$5:$A$752,0),14),"MFD")))

    Which works perfectly as intended in 2011, but when i come to save it for 2003 I have to modify the formula slightly to:

    =IF(ISBLANK($A3),"",IF(ISERROR(INDEX(Temporary!$A$5:$N$752,MATCH(A3,Temporary!$A$5:$A$752,0),14)),IF(ISERROR(INDEX(Permanent!$A$5:$N$752,MATCH(A3,Permanent!$A$5:$A$752,0),14)),"MFD")))

    However, it won't work, I get a #NAME? error.

    Can anyone point me in the right direction as to where I'm going wrong as Ive run out of ideas.

    Thanks in advance.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Formula compatability

    Watch the formula evaluate; what part gives the NAME error?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Re: Formula compatability

    Im not sure to be honest as i don't have access to Excel 2003 where I am.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Formula compatability

    Post the workbook -- or just enough to show the error.

  5. #5
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Re: Formula compatability

    example.xlsx

    Many thanks

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Formula compatability

    You've still got the IFERROR function in the formula.

  7. #7
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Re: Formula compatability

    Sorry, I thought you wanted an example of the workbook, I am unable to post an example of the workbook in 2003 with the "If/Is error" amendment.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Formula compatability

    Post back when you have what you need.

  9. #9
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula compatability

    Quote Originally Posted by Miles6978 View Post
    I have a spreadsheet created in Excel 2011, the issue I have is I need to use it on Excel 2003. In the spreadsheet i have the following formula:
    Please Login or Register  to view this content.
    Which works perfectly as intended in 2011, but when i come to save it for 2003 I have to modify the formula slightly to:
    Please Login or Register  to view this content.
    However, it won't work, I get a #NAME? error. Can anyone point me in the right direction as to where I'm going wrong as Ive run out of ideas.
    I don't know why you get a #NAME? error; perhaps a typo. But you cannot simply replace the word IFERROR with ISERROR. The two functions behave very differently.

    For backward-compatibility, the original formula should be redesigned as follows:
    Please Login or Register  to view this content.
    Note that the same formula works in Excel 2011 [sic] as well as Excel 2003, albeit less efficiently.

    Caveat: I wonder if you should write $A3="" instead ISBLANK($A3). The difference is: ISBLANK is TRUE only if $A3 is empty; that is, no constant and no formula. $A3="" is TRUE in that case, too. But it is also TRUE if the value in $A3 is the null string, for example as a result of a formula like this one.

    [EDIT] It is odd that you use $A3 in one reference, but A3 in another.
    Last edited by joeu2004; 04-07-2015 at 12:19 PM. Reason: [EDIT]

  10. #10
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Re: Formula compatability

    example.xls

    Hopefully this is of use for you, I have saved the workbook with the 97-2004 Workbook format within Excel 2011.
    Cells D3 and E3 have been changed to the IF/ISERROR amendment to the formula with the D4 and E4 cells with the original IFERROR formula. I can't get the amendment to work the same way In Excel 2003.

  11. #11
    Registered User
    Join Date
    03-23-2015
    Location
    germany
    MS-Off Ver
    2013
    Posts
    26

    Re: Formula compatability

    Quote Originally Posted by joeu2004 View Post
    I don't know why you get a #NAME? error; perhaps a typo. But you cannot simply replace the word IFERROR with ISERROR. The two functions behave very differently.

    For backward-compatibility, the original formula should be redesigned as follows:
    Please Login or Register  to view this content.
    Note that the same formula works in Excel 2011 [sic] as well as Excel 2003, albeit less efficiently.

    Caveat: I wonder if you should write $A3="" instead ISBLANK($A3). The difference is: ISBLANK is TRUE only if $A3 is empty; that is, no constant and no formula. $A3="" is TRUE in that case, too. But it is also TRUE if the value in $A3 is the null string, for example as a result of a formula like this one.

    [EDIT] It is odd that you use $A3 in one reference, but A3 in another.
    Thank you, that appears to have initially resolved the issue for me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 32 bit and 64 bit compatability
    By manofcheese in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-09-2014, 07:09 PM
  2. Compatability Mode
    By Static Cat in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-15-2013, 09:04 PM
  3. Replies: 3
    Last Post: 09-17-2011, 08:53 PM
  4. Backward compatability
    By Michael Beckinsale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2006, 12:30 PM
  5. PIA compatability
    By Irfan in forum Excel General
    Replies: 1
    Last Post: 07-15-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1