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

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

Cheers Bob,

Worked a treat

Ceptor54

"Bob Phillips" wrote:

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