I am trying to create a 'markbook' template for my school (maths faculty) in order to input all our exam marks and take the work out of manually calculating adjusted marks etc., and making something quite 'foolproof' to stop people messing up other class marks.
What I have so far is 8 sheets for the 8 classes in year 7 and a summary sheet. The summary sheet takes marks from each student's information in the 8 sheets and combines them into a long list of all the students (we often do common testing and need to see all students together). This long list also adjusts all the marks to the required percentage to calculate the overall mark for the semester.
What my big issue with all of this is, is that we often have students change classes based on an improvement or decline in ability. I'm wondering how (or if) I can keep formulas in my summary sheet working when I move a student between sheets.

e.g. Student 1 is in class B and has received 15/30 in the test. He is then moved to to class C. His row will most likely change as each class is listed alphabetically.
This would be in the 'class A' sheet
. 30
Student 1 class B 15

In the summary sheet would be all the students in class A, followed by B, then by C.

Student x class A 10
Student y class A 12
Student 1 class B 10 ---- his mark has been converted ='class B'!D4/'class B'!$D$2*20 by taking his mark and dividing by what the test it out of then changing it to a mark out of 20.
Student 2 class B 8
Student # class C 10.5
Student % class C 5

So if I move student 1 into the class C sheet, is there a way I can get excel to follow this student?
Should my summary sheet be set up somehow just to reflect each sheet somehow? Currently all the name and class inputs have just been copied in.

I hope this makes sense to someone, I can't figure out how else to explain it, and there doesn't seem to be anyone at school who knows enough about Excel to help me...

Thanks