Ok, I'm having another problem here at work and I can't get eround your
problem in any nice way.
What you can do is enter this function in cell A1
=IF(COUNT(SHEET2!$A$1:$A$500)<COLUMN();\"\";OFFSET (
SHEET2!$A$1;COUNT(SHEET2!$A$1:$A$500)-(COUNT(SHEET2!$A$1:$A$500)-COLUMN())-1;ROW()-1;1;1))
Copy cell A1 and paste in all the cells in the first line in sheet1 for
the number you need, max 500 right now.
In cell A2 you enter
=IF(COUNT(SHEET2!$B$1:$B$500)<COLUMN();\"\";OFFSET (
SHEET2!$A$1;COUNT(SHEET2!$B$1:$B$500)-(COUNT(SHEET2!$B$1:$B$500)-COLUMN())-1;ROW()-1;1;1))

and copy that to all cells in the second row in sheet1

in cell A3 you enter
=IF(COUNT(SHEET2!$C$1:$C$500)<COLUMN();\"\";OFFSET (
SHEET2!$A$1;COUNT(SHEET2!$C$1:$C$500)-(COUNT(SHEET2!$C$1:$C$500)-COLUMN())-1;ROW()-1;1;1))

and so on, note that the A in OFFSET should be A in all lines, the
other adresses should reflect the column they point to in sheet2, i e
D, E , F etc (in the COUNT statement).

Hope it works..


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551769