FORMULA TO RANK data in the order 1,2,3,... and make it dynamic

Hi to all members of the forum. I am new and I need help.

I have the following data
A B C D E
1 S/N NAME *** AVERAGE RANK
2 1 JOHN M 15
3 2 MARY F 9
4 3 PETER M 8.75
5 4 PAUL M 13
6 5 S. JONE M -
7 6 PAULINE F 9
8 7 SONCH F
9 8
10 9

I have used: =IFERROR(RANK(D2,$D$2:$D$10,0),"") to rank the average in E2

My worry is that I wish to automatically the averages in the order 1,2,3,... in sheet2 and make it dynamic such that when any average changes in column D in sheet1, the the names and sexes automatically take their rightful positions. Is there a solution to this prob?

Once your ranks are produced you can use VLOOKUP or INDEX/MATCH to feed the data in rank order into Sheet2.

If you post an example workbook we can show you how.