Thanks again, I tried this formula...

=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+((MOD(COLUMN(),( COUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=0)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+(MOD(COLUMN(),(C OUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))-1),0)

Which did copy the cells in the range from sheet 1 as long as all cels in
the specified range were filled. If one or more of the cells is empty I get
an error message. Is there any way to allow blank cells in the list range?

Thanks

"klswvu" wrote:

=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+((MOD(COLUMN(),( COUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=0)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+(MOD(COLUMN(),(C OUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))-1),0)


It is a mess... but it is working with my ranges being the same as yours.

"simon" wrote: