newbie: Copying a formula that repeats every 10 cells

[oldnich]

Sorry - I've gone braindead trying to resolve this - so forgive me if I dont express myself very well ..
I have a formula that repeats every 10 cells, that I want to copy into the next 1000 cells, but I dont want the cell reference to increase by 10 each time I copy the block, I want it to increase by 1 - Not Clear ? let me demonstrate :-

cell a1 contains the formula ' =(Sheet3!$a1) '
cell a2 contains the formula ' =(Sheet3!$b1) '
cell a3 contains the formula ' =(Sheet3!$c1) ' and so on down to
cell a10 contains the formula ' =(Sheet3!$j1)

I want to copy these 10 cells and paste into the next 1000+ cells,
What I get is
cell a11 contains the formula ' =(Sheet3!$a11) '
cell a12 contains the formula ' =(Sheet3!$b11) ' and so on.

What I want is
cell a11 contains the formula ' =(Sheet3!$a2) '
cell a12 contains the formula ' =(Sheet3!$b2) ' and so on down to
cell a20 contains the formula ' =(Sheet3!$j2) ' and then

cell a21 contains the formula ' =(Sheet3!$a3) '
cell a22 contains the formula ' =(Sheet3!$b3) ' and so on down to
cell a30 contains the formula ' =(Sheet3!$j3) ' and so on until I get to about cell a1000

How do I do That ?
Its probably easy - but Ive gone simple trying to figure it out.

[Ace_XL]

In A1

=INDEX(Sheet3!A:J,ROUNDUP(ROW(A1)/10,0),MOD(ROW(A1),10)+10*(MOD(ROW(A1),10)=0))

Copy down

[Jonmo1]

No, it's not easy....

Try this in A1 and filled down.

=OFFSET(Sheet3!$A$1,INT((ROWS(A$1:A1)-1)/10),MOD(ROWS(A$1:A1)-1,10))

DO NOT change the A$1:A1 references, they are not related to your data.

[vlady]

@ Ace & Jonmo
I merge your replies since there is a duplicate thread.thanks.

[oldnich]

Fantastic answers ...
How would you do this for 12 cells ?
eg
cell a1 contains the formula ' =(Sheet3!$a1) '
cell a2 contains the formula ' =(Sheet3!$b1) '
cell a3 contains the formula ' =(Sheet3!$c1) ' and so on down to
cell a12 contains the formula ' =(Sheet3!$L1)

I've tried expanding both the answers here - without success

[Jonmo1]

You could try changing the 10's to 12's