Lookup thru multiple worksheets with older Excel [Solved]

[Sarge]

Howdy folks,

On one hand I'm a new Excel user because I am using (ok, trying to use) more than just the basics.

On the other hand I've been using it for a long time for all kinds of jobs, keeping a list of part numbers, cost, work needed, work done, and so forth. Granted the more I read the more it seems that some of what I use it for might be better done in Access but I've not used that yet.

I have Excel 2007 at home but at work I have to use 2002.

OK, here is my problem:

I have a sales invoice on which I would like to have the price show up in the appropriate column (F) when I enter a part number in column D.

The part numbers and prices are listed in 13 separate worksheets which all follow this basic pattern: Col C lists part number, Col F lists price, row 9 is where the entries begin. Some "possible" problems: The size of the sheets range from 2-3 entries to 50 entries. There is a merged cell the width of the table between sections such as Motor Parts and Radiator Parts. Some of the part numbers have no price listed as we may not have had to purchase that part since prior to beginning to document such.

Desires, hopes, wishes:

Type part number in and have price automatically filled in if found and leaving the space blank if not found.

OR would it be better to break down and learn Access and put the part information into an actual database? There are a few overlaps on parts used and by doing so I would be able to update all vehicles that use such part with one entry, correct? If I do learn and use Access can I then have the Excel sales invoice pull the price from there?

Thanks for your patience and (hopefully!) answers.

Sarge

[ron2k_1]

I couldn't think of a better way than the attached.

I could've used VLOOKUP, but I had to include contingencies for NA values.

I could have used SUMPRODUCT but these are a bit slow and I don't know how big your lists are and how many sheets there are on your workbook.

Hopefully you can see the trend for my formula on B1 of sheet1. If you notice there is a SUMIF for each worksheet. Remember to type in the correct ID number or you'll get a $0.

EDIT: Kindly change (by editing in advanced mode) your post title to "Multiple Worksheet Lookup" or something like that. While you're there, marked your post SOLVED