Some may suggest Indirect(), which is volatile, and the inferior solution.

Try
=INDEX(Sheet2!$A$3:$Z$3,1,ROW(A1))

and copy down.

cheers,