Hi
The easiest way to achieve what you want is to transpose your source data from columns to rows.
Then use your formula as normal i.e. =Sheet1!C5 and then drag it down your target column and it will pick up the correct values from your source data.
Good luck.
Tony