You could use a formula like this in sheet 2

IF(ISERROR(SMALL(IF(Sheet1!$A1:$A20="apples",ROW($ A1:$A20),""),ROW($A1:$A20)
)),"",
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!$A1:$A20=" apples",ROW($A1:$A20),""),
ROW($A1:$A20))))

To enter it, select the number of rows you think you might need for items to
be copied, then enter the formula in the formula bar, and commit it with
Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cjjoo" wrote in
message ...

what i am doing is that i want to picked out some datas in sheet 1 that
fulfil a certain criteria(e.g those that has "apples")


but in sheet two, with the indirect function, i cant delete the blank
rows . Is there a way out for this?

the scenario:

sheet 1:
apples
oranges
apples
orange

sheet 2
apples

apples



in sheet two , i want to delete the empty rows but i realised that it
is not possible. Pls advise


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511