How do I copy a set of cells based on a logical condition

[Guest]

I've names in column A and status level (low or medium or high) in column B.

I want to copy all 'names' with 'status level' as 'low' in to a different
sheet through a formula. I do not want to do this manually.

similarly all all 'names' with 'status level' as 'medium' in to a different
sheet and so on.

I also want the value to get updated automatically if I change the status in
original sheet from 'low' to 'medium'

 

[Bob Phillips]

Select a range of cells in your target sheet, say A1:A20, that is as big as
you will ever need, then in the formula bar add

=IF(ISERROR(SMALL(IF('Sheet
1'!B1:B20="Low",ROW($B1:$B20),""),ROW($B1:$B20))),"",
INDEX('Sheet 2'!A1:A20,SMALL(IF('Sheet
1'!B1:B20="Low",ROW($B1:$B20),""),ROW($B1:$B20))))

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)