RE: Please help make IFERROR work in Excel 2003

[motion2082]

RE: Please help make IFERROR work in Excel 2003

Hey guys,

Trying to get my Excel 2003 spreadsheet to accept the formula

[U][COLOR=#0000ff]=_xlfn.IFERROR(INDEX('CAMPUS LOCATION'!$E$2:$E$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1),"")[/COLOR][/U]

https://www.dropbox.com/s/jrk5i3l8uu...-Example2.xlsx

It works in Excel 2011 but not 2003

Any ideas how to fix?

 

[Alex0013]

Re: Please help make IFERROR work in Excel 2003

Hey motion,

IFERROR did not exist in Excel 2003, it was a formula added in Excel 2007.

In order to get IFERROR to work, you have 2 options. You can add VBA to create the formula, or you can use a workaround.

The workaround would be to use the =ISERROR() formula. So something like:

=IF(ISERROR(value),"Error Message Here",value)

The VBA would be like this:

Function IFERROR(Value, Value_if_error)
    If Application.WorksheetFunction.IsError(Value) Then
        IFERROR = Value_if_error
    Else
        IFERROR = Value
    End If


End Function

If you are unsure how to add VBA, hit Alt+F11. Then on the left side right-click VBAProduct(workbookname) > Insert > Module. Next, paste in the above code.

Then go back to your workbook and =IFERROR() should work. If you open the spreadsheet in Excel 2007+ it should still work because the name of the formula is the same.

Let me know if you have any problems or need additional help!
Alex

 

[motion2082]

Re: Please help make IFERROR work in Excel 2003

Hi Alex,

I don't really understand VBA.

How about something like this, although this seems to return the Date of Completion, not the campus they were at.

=IF(ISERROR(INDEX('CAMPUS LOCATION'!$E$2:$E$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1)),"",INDEX('CAMPUS LOCATION'!$E$2:$E$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1))

 

[Alex0013]

Re: Please help make IFERROR work in Excel 2003

Hey motion,

Sorry, your formula is kind of complex without knowing the context behind it and your Dropbox link is broken.

Essentially, it should look something like

=IF(ISERROR(INDEX(...,-1)),"",INDEX(...,-1))

If your existing formula works in Excel 2007, then you should be able to copy everything inside the IFERROR, and just paste it twice withing the ISERROR in this IF statement and in the False result of the IF statement (with the True result being "")

Regards,
Alex

 

[motion2082]

Re: Please help make IFERROR work in Excel 2003

Hey motion,

Sorry, your formula is kind of complex without knowing the context behind it and your Dropbox link is broken.

This should work and make a lot more sense

https://www.dropbox.com/s/jrk5i3l8uu7pvhb/Student-Scores-Example2.xlsx

 

[Alex0013]

Re: Please help make IFERROR work in Excel 2003

Hey motion,

I pasted in your exact formula from your first post in this workbook and got the "Date started on campus". However, if I change the first range "INDEX('CAMPUS LOCATION'!$D$2:$D$20,SMALL..." to column "D" instead of "E". It works! Then I paste in the formula from Post #3 on this page (your post with the IF(ISERROR() and it works if you change the columns to column "D" as well.

So the finished formula should be below using your Example workbook.

Excel 2010

	B	C	D	E
1	Student ID	Student Name	Date Course Completed	What CAMPUS was the student LOCATED at on the Course Completion Date?
2	450	John	25-Jul-13	North
3	451	Bob	30-Apr-13
4	452	Jane	2-Apr-14
5	453	George	28-Feb-14
6	454	Ringo	15-Dec-12

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

STUDENT DETAILS​

Worksheet Formulas Cell Formula E2 =IF(ISERROR(INDEX('CAMPUS LOCATION'!$D$2:$D$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1)),"",INDEX('CAMPUS LOCATION'!$D$2:$D$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[motion2082]

Re: Please help make IFERROR work in Excel 2003

That's freakin AWESOME!!!!!

You're a genious!

 

[Aladin Akyurek]

Re: Please help make IFERROR work in Excel 2003

Hey guys,

Trying to get my Excel 2003 spreadsheet to accept the formula

[U][COLOR=#0000ff]=_xlfn.IFERROR(INDEX('CAMPUS LOCATION'!$E$2:$E$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1),"")[/COLOR][/U]

https://www.dropbox.com/s/jrk5i3l8uu...-Example2.xlsx

It works in Excel 2011 but not 2003

Any ideas how to fix?

The link does not work...

You should actually design the formula such a way that both systems accept it.

Care to state in which cell this formula has been implemented?

 

[Alex0013]

Re: Please help make IFERROR work in Excel 2003

Hey Aladin,

His issue has been resolved. I had him basically replace the IFERROR() with IF(ISERROR()) since IFERROR() didn't exist in 2003.

He should be all good

Have a great day!
Alex

 

[Aladin Akyurek]

Re: Please help make IFERROR work in Excel 2003

Hey Aladin,

His issue has been resolved. I had him basically replace the IFERROR() with IF(ISERROR()) since IFERROR() didn't exist in 2003.

He should be all good

Have a great day!
Alex

If you don't mind, what does this formula you have

IFERROR(INDEX('CAMPUS LOCATION'!$E$2:$E$20,SMALL(IF(('CAMPUS LOCATION'!$B$2:$B$20=$B2)*('CAMPUS LOCATION'!$E$2:$E$20<$D2),ROW(E$2:$E$20),""),1)-1),"")

do?