Just another option to play with, using non-array formulas
Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))
Put in C1: =IF(A1="","",IF(A1<20,ROW(),""))
Select B1:C1, copy down to C100
Col B auto-returns the required results, all neatly bunched at the top
Col C is the criteria col. Perhaps better than hardcoding the "20" in the
criteria,
for flexibility, we could simply point to a cell, say D1 for the criteria
value,
i.e. use instead in C1:
=IF(OR(A1="",$D$1=""),"",IF(A1<$D$1,ROW(),""))
In that way, we can easily change the criteria for the returns in col B
w/o having to amend and re-fill the formulas in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris_t_2k5" wrote in message
...
Here is my problem.
I have cells A1:a100 full of numbers.
I wish to select all numbers that are lower than 20 for example and paste
them into cel B1 and down.
I know i could copy and paste but I need more complicated calculations
than
that.
THEREFORE is it possible to copy cells based on af statements etc??
Thanks