Complex Dragging of cells

[DD182]

I am looking for a way to drag down a table so that the table repeats with links to diffrent sheet.

But when I drag the cells down they repeat but jump. For instance If I draged the cells bellow. It would make links to sheet2 a5 rather than a3.

It will take me a life time to do this manually, Please Help!

	1	2	3	4
Q	4 marks	3 marks	5 marks	2 marks
John	='sheet2'!a1	='sheet2'!b1	='sheet2'!c1	='sheet2'!d1
	1	2	3	4
Q	4 marks	3 marks	5 marks	2 marks
Barry	='sheet2'!a2	='sheet2!b2	='sheet2'!c2	='sheet1'!d2

<tbody>
</tbody>

 

[sijpie]

With dragging it will not work. There are a few options, but they all involve some work:
You can start with line John =Sheet2!A1 etc and drag that down for all the students
Then insert the lines and copy the 1234 and 4 marks etc and add the names

Or you can do the following: use formulas

using =INDIRECT you can build the formula that does it correctly. The following was done by entering the formulas for John, and copying these to the others:

Excel 2010
	A	B	C	D	E
1
2
3		1	2	3	4
4	Q	4 marks	3 marks	5 marks	2 Marks
5	John	5	8	8	7
6
7		1	2	3	4
8	Q	4 marks	3 marks	5 marks	2 Marks
9	Barry	6	7	7	6
10
11		1	2	3	4
12	Q	4 marks	3 marks	5 marks	2 Marks
13	Suzan	7	8	5	9
MySheet

Cell Formulas
Range	Formula
B5	=INDIRECT("'sheet2'!A" &(ROW()-5)/4+1)
B9	=INDIRECT("'sheet2'!A" &(ROW()-5)/4+1)
B13	=INDIRECT("'sheet2'!A" &(ROW()-5)/4+1)
C5	=INDIRECT("'sheet2'!B" &(ROW()-5)/4+1)
C9	=INDIRECT("'sheet2'!B" &(ROW()-5)/4+1)
C13	=INDIRECT("'sheet2'!B" &(ROW()-5)/4+1)
D5	=INDIRECT("'sheet2'!C" &(ROW()-5)/4+1)
D9	=INDIRECT("'sheet2'!C" &(ROW()-5)/4+1)
D13	=INDIRECT("'sheet2'!C" &(ROW()-5)/4+1)
E5	=INDIRECT("'sheet2'!D" &(ROW()-5)/4+1)
E9	=INDIRECT("'sheet2'!D" &(ROW()-5)/4+1)
E13	=INDIRECT("'sheet2'!D" &(ROW()-5)/4+1)

 

[DD182]

Thanks I used Indirect works perfectly