vlookup???

[Scott D]

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

 

[Don Guillett]

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)*sheet1!e2:e22)

 

[Herbert Seidenberg]

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

 

[Scott D]

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?

 

[Don Guillett]

Simply. To use the existing macro right click sheet tab>view code>insert
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