Copying cells based on pattern

Attached is the sheet that explains question

You can enter this equation into A5, and fill across. Let me know if it works:

=OFFSET(A1,0,COLUMN()-1)

Well that worked for the simpl example I attached but couldn't get it to work for the real scenerio when the data is in some other sheet. Please have a look at the attached sheet for what I am trying to accomplish. Thanks.

Enter this formula in cell B13 of Sheet1 and fill across:

=OFFSET(Sheet2!$C$5,0,7*(COLUMN()-2))

Enter this formula in Cell B14 of Sheet 1 and fill across:

=OFFSET(Sheet2!$I$4,0,7*(COLUMN()-2))

that works, thanks