Formula to create a list of people who qualify
 

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!

Formula to create a list of people who qualify
 

Lets say your Employees and criteria are on sheet1 then go to
INSERTNAMESDEFINE give it the name Under3 and add this in the formula
box *=OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B:$B), 1) *then enter
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,RO W('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 done
this way, you will then see curly brackets at the beginning and end of
the formula, once you have done this simply copy down, now all you need
to do is enter your criteria in Sheet2 A1.


Brad;259091 Wrote:
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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72354

Formula to create a list of people who qualify
 

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,RO W('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.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Lets say your Employees and criteria are on sheet1 then go to
INSERTNAMESDEFINE give it the name Under3 and add this in the formula
box *=OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B:$B), 1) *then enter
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,RO W('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 done
this way, you will then see curly brackets at the beginning and end of
the formula, once you have done this simply copy down, now all you need
to do is enter your criteria in Sheet2 A1.


Brad;259091 Wrote:
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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72354