Names of Failure Students to be Automatically Shown in the list of failures

[alyaahmed]

Hi everybody,

I prepared an excel spreadsheet for my students. It contains their marks and grades. The spreadsheet is divided into to worksheets. The first contains their marks and grades and in the second worksheet i want to include a list of those who failed. Would you please help me in finding a suitable function which will automatically add the names and data of failure students into the second worksheet. I do not want the list of failures to include any blank rows. For example, if I passed one of those who failed, his name will disappear from the list of failures and the row itself will be removed. I attached my spreadsheet so that you will get a clearer idea.

[JO505]

Does this help at all?

[alyaahmed]

To: J0505

I really thank you for your work and your functions were of great help to me. Thanks indeed. One more request, If I send you a protected excel spreadsheet, Is it possible to unprotect it?

[JO505]

The short answer is I don't know. That is not something I have had much experience with. It may be possible but I just dont have the answer for you.

Jim O

[ChemistB]

Your post does not comply with Rule 11 of our Forum RULES.

Don't post questions regarding breaking or bypassing any security measure. Such posts will be deleted, and repeat offenders will be banned. This includes posting of links to sites that offer software, code or services to do this or posting of code to remove passwords

[alyaahmed]

Thank you very much Jim.

[alyaahmed]

To ChemistB,

Sorry indeed that my request does not comply with the forum rules. It will never be repeated again.

[etaf]

does this help

EDIT
Just seen you had a reply- I had a not refreshed the page - so missed that

anyway the function is there and pulling out based on the F grade - but can be changed to work on mark values

[alyaahmed]

thanks a million etaf. My problem is solved now. grateful to you.

[alyaahmed]

Hello Etaf, Hello Everybody

Would you please do me a favour and change function so that it can work on the mark values?

[etaf]

here is the array formula
=IF(ISERROR(INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$E$3:$E$51<>"F"), 0), COLUMN(B1))),"",INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$E$3:$E$51<>"F"), 0), COLUMN(B1)))

and to change from the grade to the mark - you need to alter

=IF(ISERROR(INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$E$3:$E$51<>"F"), 0), COLUMN(B1))),"",INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$E$3:$E$51<>"F"), 0), COLUMN(B1)))

you need to change to the value of the mark - NOW for some reason , i have this working backwards !!!!!
=IF(ISERROR(INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$D$3:$D$51>59), 0), COLUMN(B1))),"",INDEX(Marks!$A$3:$D$51, MATCH(0, COUNTIF($B$2:$B2, Marks!$B$3:$B$51)+(Marks!$D$3:$D$51>59), 0), COLUMN(B1)))

and needs to then be entered as an array - so using shift+control+enter keys - to get {} brackets around the formula

attached

[alyaahmed]

Thank you very much, etaf.