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?

"klswvu" wrote:

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

error fixed... the 6 should not be hard coded