Thanks klswvu, one thing I didn't mention is that the column containing the
list on sheet 1 does have other content above it so the forumla would need
to
specify which cell in the column the list starts from. Is that possible?

Yes...

Sheet1!$A$1 is the top value of the range it can be anything ie Sheet1!$A$20

COUNTA(Sheet1!$A:$A) needs to be modified ie COUNTA(Sheet1!$A$20:$A$100) or
you can make it a defined dynamic named range.

=OFFSET(Sheet1!$A$1,((MOD(COLUMN(),(COUNTA(Sheet1! $A:$A)))=0)*(COUNTA(Sheet1!$A:$A)))+(MOD(COLUMN(), (COUNTA(Sheet1!$A:$A))))-1,0)