Extracting data from Multiple pages in workbook

I have four pages in a workbook. Each are exactly the same with different data. I need to find the "part number" (which is in a column) on each page and have it bring back the lowest cost (which is also a column). So, it looks for all part numbers across the worksheets and brings back the lowest cost. I also need for it to bring the "start" and "end" dates from the worksheet it found the lowest cost, but it is at the Header level.
We will have a master table of all parts on the first worksheet. then it looks ant the other four. More worksheets might be added.
If the part number cannot be found on any worksheet, it needs to bring in N/A.

I was going to do this in Access, but though Excel might be better because of the "Header" rows.

Thank you!

Getting data from multiple worksheets can be troublesome, especially if more worksheets are being added at intervals. If you've just started building this, it might be better to be done in Access, or in Excel without using multiple, identically structured worksheets. What kind of data are you looking at? I see that you've got part numbers, prices, and start/end dates involved. What would the the different worksheets be used for?

BTW. Headers in Excel are generally equivalent to field names in Access, so that shouldn't a deciding factor in you design choices.

The four worksheets represent individual "rebate" programs.
I need to write something that goes out, view the part number for each"rebate" then bring back the lowest price, start date and end date of program. New rebate sheets will be added and expired rebate sheets will be removed.

So, your master sheet would look something like this?
Part_ID|Part_Description|Standard_Price

and your other sheets would be something like
SheetName: Rebate_Program_XYZ
Part_ID|Rebate_Start_Date|Rebate_End_Date|Rebate_Price
?

No standard price, but yes, the rest looks good.

One last question: How are you getting the master item list?

I'm importing it from another program.

Ah well, I was hoping that it was in a company database table that you could link to -- nothing wrong with using somebody else's work to build on.

Well, either way, I'd first suggest not using separate sheets/tables for each rebate program. Instead, have one single rebate pricing sheet/table, and add a column for the rebate program. It'll keep any formulas you use in Excel much simpler: You can use one MINIFS style formula to get the price, and a INDEX-MATCH formula for the rebate program title. It's easier (depending on your relative skill levels) to do this in Access with two tables.

And just because I get bored, here's an Access version.

Create two tables.
Table one: Master_Part_List
Fields:
Part_ID --make this text and key
Part_Description --text

Table two: Rebate_Pricing
Fields:
Rebate_Offer_ID -- auto number long, key
Part_ID -- Text
Rebate_Program_Name -- text
Rebate_Start_Date -Date/Time
Rebate_End_Date --Date/Time
Rebate_Price --Decimal (or Currency on 2003/2007)

and create 3 queries.

First query is called qry_MinPriceAtDate
SQL for this is

Please Login or Register  to view this content.

Second query is called qry_MinPriceComplete
SQL

Please Login or Register  to view this content.

And third query is qry_BestPrice

Please Login or Register  to view this content.

when you run qry_BestPrice, you'll be prompted for a date and a part number, and it'll pop out the best price, the rebate program, part description, etc. If there's no applicable rebate, you won't see any of the rebate info.

Sorry for the delay in responding, I have been out of the office.
I do not want it to prompt me.... I want it to look at all part numbers and bring back a list of those part numbers with the lowest price and start...end date. We are moving this list to another database.

Thank you!