make list by using countif

[rahullambe]

Hii Friends,

I have a list of 150 employees in Row from B05:B155
& their rankings on another row from C05:C155

Now I want list of those employees whose ranking in equal to zero or less than zero in next sheet.

can anyone help e for this??????:doh:

[Kenneth Hobs]

Have you considered using AutoFilter?

[rahullambe]

No Sir,

This employee list is changing every day with rankings and names. thats why I need a formula to auto update this list...... :help

[Bob Phillips]

=IF(ISERROR(SMALL(IF((Sheet3!$B$5:$B$155<>"")*(Sheet3!$C$5:$C$155<=0),ROW($A$1:$A$151),""),ROW($A1))),"",
INDEX(Sheet3!$B$5:$B$155,SMALL(IF((Sheet3!$B$5:$B$155<>"")*(Sheet3!$C$5:$C$155<=0),ROW($A$1:$A$151),""),ROW($A1))))

and copy down as far as required.

It is an array formula.

[mikerickson]

Select A1:A150 from sheet two and enter this CSE formula in the formula bar

=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!C5:C155<=0,ROW(C5:C155),999),ROW($Z$1:$Z$200)),1)&""

This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)