Strategy for Excel Filter/Lookup

[dem1an]

I'm trying to minimize the amount of doctoring I have to do a source of raw data (spreadsheet 1). I've got spreadsheet 2 that references data in the spreadsheet 1 in order to keep the size of the file down. The problem is I need to filter the raw data down in a couple of columns (for instance filtering down to North America) before I do a vlookup from spreadsheet 2. Normally, I've done the filtering and cutting and pasting by hand, but would like to idiot proof it a little more....by either wirting a formula to do it or something in vb. I wan to to be able to use the raw data. Do you guys have some suggestions on how to do a vlookup from spreadsheet 2 after applying a filter to the data in 2 colums of spreadsheet 1? Of course VLook up sees through an autofilter.....

Thanks much!

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet.