1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index function giving a #NUM! Error

Discussion in 'Ask an Excel Question' started by MushtaqKadar, Jun 19, 2017.

  1. MushtaqKadar

    MushtaqKadar New Member

    Messages:
    4
    Below is my formula which is giving me a #NUM!. How can I fix it. I have to spreadsheet. One with the data called "Safety Stats" and the other one is actual report where I am using this formula to pull the record from the data file i.e "Safety Stats"

    =IF(INDEX('[Safety Stats.xlsx]Occupational Injuries'!$J$5:$J$128,SMALL(IF('[Safety Stats.xlsx]Occupational Injuries'!$A$5:$A$128=TEXT(EOMONTH(TODAY(),-1),"MMMM"),IF('[Safety Stats.xlsx]Occupational Injuries'!$F$5:$F$128="Employee",IF(1-ISNUMBER(MATCH('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128,{"FA - First Aid","RO - Reported Only"},0)),ROW('[Safety Stats.xlsx]Occupational Injuries'!$J$5:$J$128)-ROW('[Safety Stats.xlsx]Occupational Injuries'!$J$5)+1))),ROWS($A$2:A2)))="Struck Against","X","")

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    The file you have uploaded is not referred to in the formula ; if you mean why are cells F6:F10 displaying the #NUM! error value , unless the data referred to by the formula is available , it is difficult to say.

    One possibility is because all those cells which are displaying error values do not use the IFERROR function to hide the error values.

    Narayan
    Chirag R Raval likes this.
  3. MushtaqKadar

    MushtaqKadar New Member

    Messages:
    4
    This is what I have written with the Error function, but its not giving me the right data.
    =IF(ISERROR(INDEX('[Safety Stats.xlsx]Occupational Injuries'!$J$5:$J$128,SMALL(IF('[Safety Stats.xlsx]Occupational Injuries'!$A$5:$A$128=TEXT(EOMONTH(TODAY(),-1),"MMMM"),IF('[Safety Stats.xlsx]Occupational Injuries'!$F$5:$F$128="AREVA",IF('[Safety Stats.xlsx]Occupational Injuries'!$J$5:$J$128="Struck Against",IF(1-ISNUMBER(MATCH('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128,{"FA - First Aid","RO - Reported Only"},0)),ROW('[Safety Stats.xlsx]Occupational Injuries'!$J$5:$J$128)-ROW('[Safety Stats.xlsx]Occupational Injuries'!$J$5)+1)))),ROWS($A$2:A2)))),"","X")
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    If it is not giving you the right data , we will need to see the data for ourselves to find out what the problem is ; we cannot keep suggesting possibilities one after the other , while you keep rejecting each possibility one after the other , till we come to the solution.

    Upload your workbook.

    Narayan
  5. MushtaqKadar

    MushtaqKadar New Member

    Messages:
    4
    Attached are the 2 files for your review. In the SAM Monthly Report Test file. I am trying to achieve the following.

    I want pervious monthly records for employee only where the "Injury Type" is not "First Aid" or "Reported Only".

    Attached Files:

  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  7. MushtaqKadar

    MushtaqKadar New Member

    Messages:
    4
    It Works. Thanks a million, you are "AWSOME"

Share This Page