Try this:

sheet 2:
Header in row 1

A2:
=IF(ISERR(SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE "),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))),"" ,INDEX(INDIRECT(A$1),SMALL(IF((NAME="JD")*(PRIORIT Y="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS ($1:1))))

ctrl+shift+enter, not just enter
copy across and down as far as needed


"JRD" wrote:

Is there a function in excel 2007 and 2003 for picking only certain rows /
cells from a worksheet and copying them to a seperate worksheet in the same
document

e.g.

A B C D
NAME PRIORITY ALIVE? LAB NO

1 JD ELECTIVE Y 1
2 AN ELECTIVE N 2
3 ST URGENT Y 3
4 AN URGENT Y 1
5 JD URGENT N 2
6 JD ELECTIVE Y 3
7 ST ELECTIVE N 2

How can I pick out all the rows where column A (Name) is JD and column B
(priority) is elective and then copy the data in columns A, B and C only in
these rows only to another worksheet in the same document.

Therefore on a seperate work sheet the following would appear for the
example above:

A B C
NAME PRIORITY ALIVE?

1 JD ELECTIVE Y
2 JD ELECTIVE Y
3 ST URGENT Y

Can excel 2003 or maybe 2007 do this?

Thanks

John