Sorting data in multiple sheets using Excel 2011 for Mac

Question

I am an educator using Excel to keep track of student grades.  I would like to use a new worksheet for each assignment.  For each assignment, the first 2 rows and first 2 columns are set up as frozen panes (the columns being student names and ID numbers, the rows being components of the grade).  It is set up so that once each of the grade components is entered, the total is displayed in column H.  I have it set up to sort the the data by student name or number, and all grades move with the other information. 

I want to create a total of 6 sheets for different assignments that are formatted exactly the same way.  However, there are often students added to or removed from the class.  I would like to be able to add/remove/sort students from one worksheet, and have it applied to all other worksheets and sort the grades so that they remain on the same row with the correct students' name/ID number.

I am not super advanced in Excel and have tried a couple of things already.  I tried going to the second sheet and inputting student names/ID numbers with "=Sheet1!A3", etc.  When I sort the columns in sheet 1, it will change the student names/ID numbers in the following sheets, but the grades do not move, and are therefore incorrect.

Is there a way to make this happen? 

Any help is greatly appreciated.

I thought of a simpler way to ask this.  I want multiple worksheets, with columns 1 and 2 to be the same data.  I want to be able to sort the data in the first worksheet, and have it sort the data in all other worksheets, but it sorts the data from multiple columns from each worksheet based on the first 2 columns of worksheet 1.  I hope this makes more sense.  Again, any help is greatly appreciated.

Answer 1

Excel can't do it the way you are describing. You can indeed get a list to expand and contract but the formulas are a little passed Excel 101. Did you know that you can edit multiple sheets at the same time by selecting multiple sheet tabs? You discovered how to put in formulas to link one sheet to the other to keep the student lists the same. however to match them up with the grades as the first list gets sorted you will need MATCH, VLOOKUP or a combination thereof to keep the grades with the student(s).

It is also difficult to visualize exactly what you have and what you intend. If you can post a sample it would be a big help.

Answer 2

I'm not trying to list only students who did not do the assignment.  I want every student listed.  The problem is that the list of students changes constantly, and I don't want to change the students in every single workbook over and over again.  I want to be able to change the list of students on one sheet, and have all of the data from the other sheets be able to sort accordingly. 

Excel really can't do that?  I mean, I am willing to accept that if it's the case, but I am skeptical that it can do all of these other crazy formulas and this function would not be included somehow.

Answer 3

Excel can't do that. You should make all your sheets identical in terms of students. So every student is showing up in every sheet even though they did not do that assignment. Then Sorting will make more sense.