Conditional List
 

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

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

Conditional List
 

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