• 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...

  • 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

MushtaqKadar

New Member
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","")
 

Attachments

  • Safety Stats Image.xlsx
    12.4 KB · Views: 5
  • Sample report.xlsx
    20.6 KB · Views: 5
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
 
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")
 
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
 
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".
 

Attachments

  • Safety Stats.xlsx
    15.4 KB · Views: 2
  • SMA Monthly Reports Test.xlsx
    198.5 KB · Views: 3
Back
Top