+ Reply to Thread
Results 1 to 9 of 9

IFERROR INDEX script stop working

  1. #1
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    111

    IFERROR INDEX script stop working

    Good afternoon everyone

    I got help setting up this Formula a few months ago and the last week it stopped working
    I'm very new at index match formulas learning as I go
    the code below is in Sheet Timeslot Tracker report Cell M2 and goes down the page
    Please Login or Register  to view this content.
    The problem I have is it is showing LOAD NUMBER NOT FOUND! on everything even when the load number is on Date sheet

    When I do a check on Date I can see the load number there not sure why this is getting a error
    If this code needs to be changed in any way I don't mine

    any help would be great
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,966

    Re: IFERROR INDEX script stop working

    Maybe try
    M2
    =IFERROR(INDEX(Date!D:D,AGGREGATE(15,6,ROW(TMS_nr)/(TMS_nr=$B2),1)),"LOAD NUMBER NOT FOUND!")

    N2
    =IFERROR(INDEX(Date!L:L,AGGREGATE(15,6,ROW(TMS_nr)/(TMS_nr=$B2),1)),"LOAD NUMBER NOT FOUND!")
    Attached Files Attached Files
    Bo

  3. #3
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    111

    Re: IFERROR INDEX script stop working

    Good afternoon Bo_Ry

    Thank you for your help working great on the test sheet via office 365

    I have just tested this formula on another few sheet getting the same errors I have uploaded a copy for you

    I don't understand as the information on both sheets match

    p.s I forgot to take out all the VBA coding on that last excel sheet let me know if you like me to reupload it with no vba in there
    Attached Files Attached Files
    Last edited by justinwb; 03-16-2019 at 01:42 AM.

  4. #4
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,966

    Re: IFERROR INDEX script stop working

    The number in column B is number Stored as Text

    ="1"=1 gives Fasle
    =--"1"=1 gives True

    Untitled.jpg

    add -- before $B2 to change text to number
    M2
    =IFERROR(INDEX(Date!D:D,AGGREGATE(15,6,ROW(TMS_nr)/(TMS_nr=--$B2),1)),"LOAD NUMBER NOT FOUND!")
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    111

    Re: IFERROR INDEX script stop working

    Bo_Ry

    You are the best
    I never understood why excel cant see the difference from text and numbers as to us they look the same

    I just run use this on a few of the sheets some work and some don't I think I have a mix of both text and numbers mixed in with each other on some of the sheets
    I have another company that provides the information

    Is there a way for this formula to see both text and numbers and return all the information

    I have this set up inside VBA which I run every morning

    Please Login or Register  to view this content.
    looking back at the old code
    Please Login or Register  to view this content.
    B2&"" and B2= looks like it tried to tell that difference from text to numbers then it stopped working

  6. #6
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,966

    Re: IFERROR INDEX script stop working

    B2&"" change Text, number to Text
    --B2 change Text, number to number but if B2 is not number eg: --"a" will give error #VALUE!

    Your data in TMS_nr, Date! column AB is number, so use -- to match data type
    or change both to text by

    =IFERROR(INDEX(Date!D:D,AGGREGATE(15,6,ROW(TMS_nr)/(TMS_nr&""=$B2&""),1)),"LOAD NUMBER NOT FOUND!")

  7. #7
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    111

    Re: IFERROR INDEX script stop working

    Bo_Ry

    You are the best
    I never understood why excel cant see the difference from text and numbers as to us they look the same

    I just run use this on a few of the sheets some work and some don't I think I have a mix of both text and numbers mixed in with each other on some of the sheets
    I have another company that provides the information

    Is there a way for this formula to see both text and numbers and return all the information
    I have uploaded another sheet that has both mixed one after testing both Formula
    I have place each formula in there own column M and O for you so you can see side by side results

    I have conditional formatting set up when a result returns as this report gets sent off to our customer every 2 hours

    I have this set up inside VBA which I run every morning

    Please Login or Register  to view this content.
    looking back at the old code
    Please Login or Register  to view this content.
    B2&"" and B2= looks like it tried to tell that difference from text to numbers then it stopped working
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,966

    Re: IFERROR INDEX script stop working

    Change both to text

    =IFERROR(INDEX(Date!D:D,AGGREGATE(15,6,ROW(TMS_nr)/(TMS_nr&""=$B2&""),1)),"LOAD NUMBER NOT FOUND!")

  9. #9
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    111

    Re: IFERROR INDEX script stop working

    This is Awesome Bo_Ry

    I have not heard of the AGGREGATE formula I will look in to this
    I run all of the last 2 weeks and none of them gave load number not found

    I will update my VBA will let you know if I run in to any problems

    Thanks for all your help and support
    JustinwB

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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