Hyperlink between two workbooks

[demondave]

I am a novice excel 2003 user. I would appreciate some help in correctly setting up a hyperlink between two workbooks. The first Master workbook is provided to me weekly by employer. The workbook is similar from week to week, however the information I am utilizing from this book for formula calculations is not always in the same cell locations and dollar amount of product costs change example:

Week 1
A B C D
1 Item Description Cost Margin
2 10859 Pepperoni $25.00 10%
3 #####

Week 2
A B C D
1 Item Description Cost Margin
2 #####
3 #####
4 10859 Pepperoni $24.50 10%


My Master workbook contains over 3000 inventory items and I am setting up a separate Selling workbook with hyperlinks to the Master workbook, to identify key products by their Item number and automatically calculate a selling price based on cost + desired margin.

I discovered a post that helped me use naming the range in column A: ='[Master Price Book Spreadsheet.xls]3-6'!$A:$A. This allows me to overwrite the previous weeks Master Workbook in my documents with out having to adjust Master Cell locations in my Selling workbook formula every week. The following formula identifies the item I am pricing from the named range:
=countif(ITEM,"10859").

My question is once my Selling workbook identifies the Item I price every week, how can I get its destination cell to display the correct selling price without my having to manually adjust the Master Workbook cell location that contains my cost. Currently, I am getting an accurate number with the following formula:
=countif(ITEM,"10859")*('[Master Price Book Spreadsheet.xls]3-6'!$C$C4*1.1), but each week I need to change the part of the formula that identifies the location of the items cost.

I would appreciate advice on how to Name the calculation that will search for the price in my cost column associated with the item # I am trying to provide a selling cost for?

Thanks,
Demon

 

[jbeaucaire]

INDEX/MATCH is going to be your ticket, I think. To find the price, use this:

=INDEX($C:$C,MATCH(10859,$A$A,0))
or
=INDEX($C:$C,MATCH(10859,ITEM,0))

That creates an index of all the prices in column C, then finds the row in column A with the right code, giving you that value out of column C.

The zero at the end means "First exact match found".

 

[demondave]

Thank you for replying. How does the cell know which workbook to use as the source of data?

demon

 

[demondave]

Never mind. Got it. Thank you sooo much for your assistance. I really appreciate the help.