Thanks Lalit. I did as you suggested, but in every cell, there is an error - #NAME?.
Revised book is attached. Please guide/do the needful. Thanks a ton for suggestions.
No idea, where i've misdirected myself!
Printable View
Thanks Lalit. I did as you suggested, but in every cell, there is an error - #NAME?.
Revised book is attached. Please guide/do the needful. Thanks a ton for suggestions.
No idea, where i've misdirected myself!
I'm closing this thread, as it is. Thanks for all the support, fellow members. I'm also exiting from this forum, trust no offence.
Once again thanks for all the support that you all extended to me.
Kindly suggest, how do i delete my profile / Deactivate My Account from this Forum
I'll be thankful to the moderators too, if they can directly delete my account/membership, without intimation to me.
All formulas are array formula so what is missing in your file is:
First select the range A3:A17 Press F2 and then Ctrl + Shift + Enter
Try this and it will solve your problem.
@Lalit,
Thanks Brother. I did Ctrl+Shift+Enter to effect formula as Array Formula, but still error persist.
Although the solution which i provided you is perfectly working for me, i think the only issue is you are not familiar with array formula so you can google it.
Here i am posting a new solution based on array formula :) with some changes
Try this:
For Current Year Table (Change Total Mark Column header to Total Marks)
Step 1:
In Cell A3 put below formula and press Ctrl + Shift + Enter and now drag A3 formula down till you need it
Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score RankCode:=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Pass"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(A$3:A3)),MATCH(A$2,$A$19:$F$19,0)),"")
Step 2:
In Cell D3 put below formula and press Ctrl + Shift + Enter and now drag D3 formula down till you need it
Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score RankCode:=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Fail"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$A$19:$F$19,0)),"")
For 5 Year Table (Change Total Mark Column header to Total Marks)
Step 1:
In Cell H3 put below formula and press Ctrl + Shift + Enter and now drag H3 formula down till you need it
Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score RankCode:=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Pass"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")
Step 2:
In Cell K3 put below formula and press Ctrl + Shift + Enter and now drag K3 formula down till you need it
Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score RankCode:=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Fail"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")
As all these are array formula so make sure you are applying the formula with Ctrl + Shift + Enter