#### demondave

##### New Member
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### jbeaucaire

##### Well-known Member
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

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

demon

#### demondave

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

Replies
2
Views
389
Replies
1
Views
426
Replies
4
Views
675
Replies
5
Views
518
Replies
3
Views
145

1,190,817
Messages
5,983,060
Members
439,818
Latest member
schizoid231

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back