[ms office - 2013] Help with Excel sorting problem

[sparkeh]

So, in true software development style, I have finished a project and the bosses have asked for a new feature that I can't work out how to add.

 

For a greatly simplified example, imagine three sheets in Excel, Sheet 1 contains a list of pupil names, Sheet 2 contains pupil names and a set of results, Sheet 3 contains pupil names and a summary of results from Sheet 1.

 

E.g. Sheet 1

[TABLE=width: 500]

[TR]

[TD]Surname[/TD]

[TD]Firstname[/TD]

[/TR]

[TR]

[TD]A[/TD]

[TD]Pupil[/TD]

[/TR]

[TR]

[TD]B[/TD]

[TD]Pupil[/TD]

[/TR]

[TR]

[TD]C[/TD]

[TD]Pupil[/TD]

[/TR]

[/TABLE]

 

 

Sheet 2

[TABLE=width: 500]

[TR]

[TD]Surname[/TD]

[TD]Firstname[/TD]

[TD]Result 1[/TD]

[TD]Result 2[/TD]

[TD]Result 3[/TD]

[TD]Result 4[/TD]

[TD]Result 5[/TD]

[TD]Summary[/TD]

[/TR]

[TR]

[TD]A[/TD]

[TD]Pupil[/TD]

[TD]1[/TD]

[TD]1[/TD]

[TD]1[/TD]

[TD]1[/TD]

[TD]1[/TD]

[TD]1[/TD]

[/TR]

[TR]

[TD]B[/TD]

[TD]Pupil[/TD]

[TD]2[/TD]

[TD]2[/TD]

[TD]2[/TD]

[TD]2[/TD]

[TD]2[/TD]

[TD]2[/TD]

[/TR]

[TR]

[TD]C[/TD]

[TD]Pupil[/TD]

[TD]3[/TD]

[TD]3[/TD]

[TD]3[/TD]

[TD]3[/TD]

[TD]3[/TD]

[TD]3[/TD]

[/TR]

[/TABLE]

The cells containing the pupil names just references to the relevant cells on Sheet 1. Results are entered on this sheet.

 

Sheet 3

[TABLE=width: 500]

[TR]

[TD]Surname[/TD]

[TD]Firstname[/TD]

[TD]Summary[/TD]

[/TR]

[TR]

[TD]A[/TD]

[TD]Pupil[/TD]

[TD]1[/TD]

[/TR]

[TR]

[TD]B[/TD]

[TD]Pupil[/TD]

[TD]2[/TD]

[/TR]

[TR]

[TD]C[/TD]

[TD]Pupil[/TD]

[TD]3[/TD]

[/TR]

[/TABLE]

The results summary on Sheet 3 are just simple references to the relevant cells on Sheet 2.

 

Currently if new pupils are added on Sheet 1 a macro sorts sheet 2 which in turn makes sure that Sheet 3 is sorted.

 

The new feature required is to have a new column at the end of Sheet 3 for the Headteacher to add comments about the pupils' achievement.

My problem with this is that if a new pupil is added then the comments column will be out of sync with the correct pupil names as the rest of the information is being sorted on other sheets.

 

Any ideas how to keep the comments in line with the pupils?