Try this formula in the Sheet 1 cells...
=OFFSET(Sheet2!$C1, 0, 5 * (COLUMN()-1))
By way of explanation
COLUMN() will return the column number for the cell containing the formula so for
A1 B1 C1 D1
1 2 3 4 etc...
The data you want is every 5th column (once the pattern starts) hence the
5 *
OFFSET returns the value from the cell (in this case)
0 rows away from the initial starting point (C1) and the calculated number of columns away from the same starting cell.
The
-1 is because in A1 I want the value of the starting cell and the
$C1 is because I always want to calculate from the same point (otherwise Excel will change this to D, E etc as you drag the formula across)
Edit - changed the sheet number to the correct one!! Sorry about that.
Submit an article or tip