Excel Formula really simple one

[prasadsg2005]

Hello Buddies,
I have a Sheet with 2 tabs in it. 1st tab contains Names/Date/Number. I copied the same data and pasted in respective columns. like below

Name	Date	Number	Left
Prasad	20/07/2013	1312	22/07/2013
Somesh	21/07/2013	N007	25/07/2013

<TBODY>
</TBODY>

Now in the second tab the heading are the same but there are some more rows which i have to fill in
Sheet 2 Looks like below

Name	Date	Number	Left	Comments
Prasad	20/07/2013	1312	22/07/2013	A
Prasad	20/07/2013	1312	22/07/2013	B
Prasad	20/07/2013	1312	22/07/2013	C
Prasad	20/07/2013	1312	22/07/2013	D
Somesh	21/07/2013	N007	25/07/2013	A
Somesh	21/07/2013	N007	25/07/2013	B
Somesh	21/07/2013	N007	25/07/2013	C
Somesh	21/07/2013	N007	25/07/2013	D

<TBODY>
</TBODY>

Now is sheet 2 i can simply go and apply formula as ='Sheet1'!A2/B2/C2/D2 all well till here.
But when i Drag it down it takes ='Sheet1'!A3/B3/C3/D3 Which is wrong as i need the same persons data must be repeated for 4 times. Abd then the next persons Data must come.

I have to make such kind of file on every monday and have around 200 peoples data in it and copying and pasting data takes hell lot of tim.


Can the EXPERTS help me in this ??

 

[Joe4]

I wouldn't exactly call it a real simple one, but if you know a few "tricks", it can be done.

Use the INDIRECT formula. Enter formula in A2 and copy down for all rows:
=INDIRECT("Sheet1!A" & INT((ROW()-2)/4)+2)

For column B, simply change the "A" to a "B", paste in cell B2 and copy down for all rows:
=INDIRECT("Sheet1!B" & INT((ROW()-2)/4)+2)

etc.