I am designing a marking speadsheet which is built up of 50 worksheets (one for each student) and a master page which I wish to gather all the information together. All 50 Student worksheets have exactly the same layout and the positions of each cell (e.g mark obtained) is the same on each worksheet.

In the master spreadsheet I am trying to create a reference system that will bring all the data together. For example I want 50 columns (one for each student) and then rows for each exam question. The formula I am using to reference the cells, for example to reference the mark for student 1, question1 in the master spreadsheet is =('Student 1'!$D6). I want to be able to copy this formula across columns so that the subsequent columns will have the formulae =('Student 2'!$D6), =('Student 3'!$D6) etc

My problem is that when I copy the cells across in the master spreadsheet, I cannot get the Student 1 bit of the formulae to change to Student 2 etc. The formulae just remains with =('Student 1'!$D6) across the entire row.

For now I am manually entering the references and this is ok for 50 worksheets but will be a slight problem with 1000 worksheets!. Is there a way of copying the formuale across and the formuale changes.

Any Solutions if possible would be greatly appreciated
Cheers