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
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Number
[/TD]
[TD]Left
[/TD]
[/TR]
[TR]
[TD]Prasad
[/TD]
[TD]20/07/2013
[/TD]
[TD]1312
[/TD]
[TD]22/07/2013
[/TD]
[/TR]
[TR]
[TD]Somesh
[/TD]
[TD]21/07/2013
[/TD]
[TD]N007
[/TD]
[TD]25/07/2013
[/TD]
[/TR]
</TBODY>[/TABLE]

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
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Number
[/TD]
[TD]Left
[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]Prasad
[/TD]
[TD]20/07/2013
[/TD]
[TD]1312
[/TD]
[TD]22/07/2013
[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Prasad
[/TD]
[TD]20/07/2013
[/TD]
[TD]1312
[/TD]
[TD]22/07/2013
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Prasad
[/TD]
[TD]20/07/2013
[/TD]
[TD]1312
[/TD]
[TD]22/07/2013
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Prasad
[/TD]
[TD]20/07/2013
[/TD]
[TD]1312
[/TD]
[TD]22/07/2013
[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Somesh
[/TD]
[TD]21/07/2013
[/TD]
[TD]N007
[/TD]
[TD]25/07/2013
[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Somesh
[/TD]
[TD]21/07/2013
[/TD]
[TD]N007
[/TD]
[TD]25/07/2013
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Somesh
[/TD]
[TD]21/07/2013
[/TD]
[TD]N007
[/TD]
[TD]25/07/2013
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Somesh
[/TD]
[TD]21/07/2013
[/TD]
[TD]N007
[/TD]
[TD]25/07/2013
[/TD]
[TD]D[/TD]
[/TR]
</TBODY>[/TABLE]

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.