Sort list of data taken from another source based on certain criteria

[Scott_88]

Hi

Looking for some help.

Attached is a test sheet

Test.xls

I have a list of staff members and the teams they fall under. Within the list are members of staff who have left. Also listed is the performance score of each staff member for each month of the year.

I have a worksheet which lists the members of staff who still work with the company and another worksheet which lists those who have left the company. On each sheet is their performance score for each month. Each worksheet displays an overall average for the year and a rank. I am looking to sort the rank column in both sheets however, due to the formula I have used to list the names of the staff members, it will not allow me to do this.

In order to list those staff members who are still with the company I have used: {=IF(ISERROR(INDEX(Staff_Column,SMALL(IF(Team<>"Leaver",ROW(Team)),ROW(1:1)))),"",INDEX(Staff_Column,SMALL(IF(Team<>"Leaver",ROW(Team)),ROW(1:1))))}

In order to list those who have left, I have used:
{=IF(ISERROR(INDEX(Staff_Column,SMALL(IF(Team="Leaver",ROW(Team)),ROW(1:1)))),"",INDEX(Staff_Column,SMALL(IF(Team="Leaver",ROW(Team)),ROW(1:1))))}

Is their any way to list those who remain with the company and those who have left and be able to sort this list based on their performance score?

Thanks

Scott