Transferring only certain rows to seperate worksheet

[JRD]

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

 

[JRD]

Hi Mike,

Many thanks

Excuse my ignorance, but what is worksheet code and how and where do I enter
it. Do I put it in a cell?

John

 

[Teethless mama]

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")*(PRIORITY="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))))

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

 

[Mike H]

Hi,

On the sheet with the source data, right click the sheet tab, view code and
paste the code in on the right. In the code change DestSheet to the worksheet
you want the data pasted into and then run the code by pressing F5

Mike

 

[Ashish Mathur]

Hi,

If you are looking for a non formula based approach, you may use advanced
filters. Assume that the data is in range A18 (including headings). In
A11:B11, type name and priority. In A12:B12, type JD and Elective. Now go
the next worksheet and type Name, Priority, Alive in range B44. Now click
on cell B6 of this sheet and go to Data > Filter > Advanced Filter. In the
Action group, select copy to another location. in the list range, select
A18 of the previous sheet (where the data is). In the criteria box,
select A11:B12 of the sheet where the data is. In the copy to box,
highlight B44 of the Output sheet. Now click on OK.

Please note that this is not a dynamic solution. Everytime the base data
changes, you will have to rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com