Formula to create a list of people who qualify

[Brad]

I am trying to create a list of employees who meet a criteria and would like
the names to be added to another list in a new excel sheet.

Employee name is in cell A2:A100
Criteria is in cell d2:d100

criteria is less than -3%

Thanks!

 

[Simon Lloyd]

Lets say your Employees and criteria are on sheet1 then go t
INSERT>NAMES>DEFINE give it the name Under3 and add this in the formul
box *=OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B:$B),1) *then ente
this in sheet2 A2
*=IF(ISERROR(SMALL(IF(Under3=$A$1,ROW('Sheet1!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))),"",INDEX('Sheet1'!$A$2:$A$100,SMALL(IF(Under3=$A$1,ROW('Sheet1'!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))))
and commit the ARRAY formula using Ctrl+Shift+Enter, it must be don
this way, you will then see curly brackets at the beginning and end o
the formula, once you have done this simply copy down, now all you nee
to do is enter your criteria in Sheet2 A1

I am trying to create a list of employees who meet a criteria and woul
lik
the names to be added to another list in a new excel sheet

Employee name is in cell A2:A10
Criteria is in cell d2:d10

criteria is less than -3

Thanks

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[T. Valko]

Some thoughts:

=IF(ISERROR(SMALL(IF(Under3=$A$1,ROW('Sheet1!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))),"",INDEX('Sheet1'!$A$2:$A$100,SMALL(IF(Under3=$A$1,ROW('Sheet1'!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))))

The error trap can be written like this which is much more efficient and a
lot shorter:

=IF(ROWS(A$2:A2)<=COUNTIF(Under3,$A$1),INDEX(...),"")

Instead of calculating an array of offsets:

ROW('Sheet1'!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1

You can calculate a single offset like this:

SMALL(IF(Under3=$A$1,ROW($A$2:$A$100)),ROW(A1))-MIN(ROW($A$2:$A$100))+1

Using ROWS(A$1:A1) is more robust than using ROW(A1). ROW(A1) is more
vulnerable to row insertions which could "break" the formula. For example,
if you insert a new row 1 ROWS(A$1:A1) becomes ROWS(A$2:A2) and still
evaluates to 1 but ROW(A1) becomes ROW(A2) which evaluates to 2 and now
you'll miss the first instance of the criteria.