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

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" <Ceptor54@discussions.microsoft.com> wrote in message
news:402BEF8C-123A-466E-9892-FE604B050556@microsoft.com...
> 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" <Ceptor54@discussions.microsoft.com> wrote in message
> news:402BEF8C-123A-466E-9892-FE604B050556@microsoft.com...
> > 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
>
>
>