Conditional List
Select your target range in sheet 2, say A1:A20, and enter this formula in
the formula bar
=IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$20=1,ROW($A1:$ A20),""),ROW($A1:$A20))),"
",
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!$B$1:$B$20 =1,ROW($A1:$A20),""),ROW($
A1:$A20))))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Ceptor54" wrote in message
...
I am trying to auto generate a list on a worksheet based on conditions in
another i.e.
Worksheet 1 Contains the list below,
tree 1
Car 2
Fruit 1
tea 1
I want a list to be generated in worksheet2 containing just,
Tree
Fruit
Tea
I was hoping to do this with in sheet formula as opposed to macro etc
(don't
understand them)
Thanks in Advance
Ceptor54
|