I've studied a lot of post and can't find what i need so here goes. Forgive
me if i missed it somewhere.

sheet 1 contains the following data:

A B C D E
1 Date Invoice# Job Name Class Amount
2 4/5/09 546321 South Rough 23.16
3 4/6/09 546322 South Trim 57.42
4 4/7/09 546323 South Rough 89.30
5 4/7/09 546324 North Rough 1.23

I want sheet 2 to look like the following:

A B C D E
1 Rough
2 South
3 Date Invoice# Job Name Class Amount
4 4/5/09 546321 South Rough 23.16
5 4/7/09 546323 South Rough 89.30


because I told it to search sheet1!A2:E5and find whatever criteria that I
input into Sheet2 A1 and A2.
Then return the row in sheet1 that met both criteria from a1 and a2 to the
next available row on sheet 2 (ignoring the header rows)
I hope this makes sense...it does in my head :-)

I would probably use a macro but
=sumproduct(sheet1:c2:c22="rough")*(sheet1!d2:d22= "south")*sheet1!e2:e22)
or
=sumproduct(sheet1:c2:c22=a1)*(sheet1!d2:d22=a2)*s heet1!e2:e22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott D" wrote in message
...
I've studied a lot of post and can't find what i need so here goes.
Forgive me if i missed it somewhere.

sheet 1 contains the following data:

A B C D E
1 Date Invoice# Job Name Class Amount
2 4/5/09 546321 South Rough 23.16
3 4/6/09 546322 South Trim 57.42
4 4/7/09 546323 South Rough 89.30
5 4/7/09 546324 North Rough 1.23

I want sheet 2 to look like the following:

A B C D E
1 Rough
2 South
3 Date Invoice# Job Name Class Amount
4 4/5/09 546321 South Rough 23.16
5 4/7/09 546323 South Rough 89.30


because I told it to search sheet1!A2:E5and find whatever criteria that I
input into Sheet2 A1 and A2.
Then return the row in sheet1 that met both criteria from a1 and a2 to the
next available row on sheet 2 (ignoring the header rows)
I hope this makes sense...it does in my head :-)

Excel 2007
Advanced Filter
Macro
http://www.mediafire.com/file/yjdznynjywj/04_21_09.xlsm

This code will do what I want but due to my lack of knowledge and experience
in VB, I'm having a tough time customizing it to my worksheet layout.
Instead of my criteria being located in a table (table3), I would like the
criteria to be located in individual cells anywhere I choose and I don't
know how to change the syntax to reflect this. I would also like the code
to fire or execute whenever any action occurs in the workbook or at least
when a new criteria is selected from the dropdown list in these designated
cells. (which I do like...the dropdown lists) as opposed to the button. I
would also like to have more than one sheet with filtered results to allow
for easier viewing and printing but the source sheet would remain the same.
Hope this doesn't confuse and thankyou so much for your time. one last
request, could you include a few more notes in your code so i can understand
what each line is doing. some of it is logical and easy to deduce, but a
lot of it is greek to me. I really would like to understand vb better. any
learning sources?
"Herbert Seidenberg" wrote in message
...
Excel 2007
Advanced Filter
Macro
http://www.mediafire.com/file/yjdznynjywj/04_21_09.xlsm

Simply. To use the existing macro right click sheet tabview codeinsert
this.
Now when you change EITHER c4 or c5 the macro will be called.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c4:d5")) Is Nothing Then
Call AdvFilter
End If

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott D" wrote in message
...
This code will do what I want but due to my lack of knowledge and
experience in VB, I'm having a tough time customizing it to my worksheet
layout. Instead of my criteria being located in a table (table3), I would
like the criteria to be located in individual cells anywhere I choose and
I don't know how to change the syntax to reflect this. I would also like
the code to fire or execute whenever any action occurs in the workbook or
at least when a new criteria is selected from the dropdown list in these
designated cells. (which I do like...the dropdown lists) as opposed to the
button. I would also like to have more than one sheet with filtered
results to allow for easier viewing and printing but the source sheet
would remain the same. Hope this doesn't confuse and thankyou so much for
your time. one last request, could you include a few more notes in your
code so i can understand what each line is doing. some of it is logical
and easy to deduce, but a lot of it is greek to me. I really would like
to understand vb better. any learning sources?
"Herbert Seidenberg" wrote in message
...
Excel 2007
Advanced Filter
Macro
http://www.mediafire.com/file/yjdznynjywj/04_21_09.xlsm