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.

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

File size:
12.4 KB
Views:
4
File size:
20.6 KB
Views:
4
2. ### NARAYANK991Excel Ninja

Messages:
15,625
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.

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. ### NARAYANK991Excel Ninja

Messages:
15,625
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.

Narayan

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

File size:
15.4 KB
Views:
1
File size:
198.5 KB
Views:
2
6. ### NARAYANK991Excel Ninja

Messages:
15,625
Hi ,

See if this is OK.

Narayan

File size:
16 KB
Views:
0
File size:
189.9 KB
Views:
1