Linked cells no longer link when list is alphabetised

Hi everyone - hoping someone might be able to help with my dilemma.

Within my spreadsheet, I have one sheet that is linked with another (i.e. typed values Sheet 2 returns values in Sheet 1). Sheet 2 runs a macro that alphabetises names and the data moves with the names; the problem that I have is that when the list is alphabetised and the names change row, the formulas typed in Sheet 1 to return the data from Sheet 2 no longer reference the correct cell and so the incorrect data is pulled over.

Is there a way to keep the row number the same and so the numbers are out of order on the left hand side of the spreadsheet but the reference, therefore, is maintained? Or is there another way to combat this?

Thanks for your help.

Thank you for your reply.

I've attached a simplified version of the spreadsheet to hopefully show what I mean.

In the spreadsheet are two class sheets and one maths group sheet illustrating before my problem occurs and a copy of each sheet showing the problem after.

Next to each name, I have a maths set to put them in (for the purposes of the explanation, only group 1 is included). So when 'Maths Set' shows "1", this populates the names into this maths sheet.

The maths sheet is then linked to each class sheet by formula of =MAX because of the 4 maths sets I want the highest value returned to the class list cell.

So in the 'BEFORE' sheets you can see that the data inputted into the maths sheet populates in the class list next to the corresponding names. Once the maths list has been alphabetised, the names change row number in this sheet and so no the numbers no longer populate in the correct cell in the class list sheet because the row number has changed and the =MAX formula is incorrect for that cell number.

Is there a way that when the row changes number, the correct numbers still populate next to the corresponding names?

Thank you very much for your time.