Keeping cell value with row even when column changes - in between workbooks!!!

[fecndcnt]

Hello all,

I am putting together workbooks for my herbal product business, and am using excel to keep track of supply costs, and final costs.

I have made a workbook entitled "SUPPLY - Price List" which has the prices for my supplies broken down by units. for example:

(1) Arnica Flowers $6.44
(2) Black Walnut $0.91
(3) Butcher's Broom $1.07
(4) Calendula Flowers $1.79
(5) Cayenne $0.91

I then want to link the value to another workbook where I hold my the ingredients of my products. So for example:

(1) Arnica Flowers $6.44
(3) Butcher's Broom $1.07
(4) Calendula Flowers $1.79
Product total: $9.30

I have figured out how to link the supply price to the ingredients list so that if the price of one of my supplies changes, it will automatically change in the ingredient list. the problem I have yet to figure out is how to link the value to the cell even when it moves. Let's say I add a supply:

(1) Arnica Flowers $6.44
(2) Black Walnut $0.91
(3) Butcher's Broom $1.07
(4) Calendula Flowers $1.79
(5) Cayenne $0.91
(6) Acai Powder $3.88

All is well. But if I want to alphabetize:

(1) Acai Powder $3.88
(2) Arnica Flowers $6.44
(3) Black Walnut $0.91
(4) Butcher's Broom $1.07
(5) Calendula Flowers $1.79
(6) Cayenne $0.91

All the values shift but the linked cell remains in place. Thus the ingredient list incorrectly reflects the price:

(1) Arnica Flowers $3.88
(3) Butcher's Broom $0.91
(4)Calendula Flowers $1.07
Product total: $5.86

How do I make sire that the value stays with the right row, even when it shifts in the column?

Also, please excuse any my lack of proper terminology. I am completely self taught, but a quick learner. I am not advanced, but no need to respond as if I am a novice. Thanks for your time! Hopefully I have explained what I am looking to do well enough.

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[fecndcnt]

Thank you for your quick response! I hope this is what you are looking for.

I made two workbooks to further show what I am doing. I want the "price per oz." to correspond to the "ITEM", regardless of where it falls on the sheet. So if I were to add an "ITEM" and rearrange the supply list, the product cost would connect to the proper item's price, not the cell in which it formerly sat. Hope this helps!

[dflak]

I am going to suggest two things: VLOOKUP and Excel Tables.

And I have a question: do you have an absolute need to have this in two workbooks. The issue with two workbooks is that without VBA, both books have to be open.

The following article should get you started with Tables and how to make them. http://www.utteraccess.com/wiki/inde...ables_in_Excel.

In the Supply Workbook, I converted the data to an Excel Table. One of the advantages of Excel tables is that they know how big they are so you don't have to guess how many rows to include in your formulas. You could include the entire column but then you are evaluating a million rows of data when you might only need 100 or 1,000 or 10,000. This slows execution down a lot. On the other hand you could guess that you'll need 100 rows and when the 101 row shows up, you miss data. With tables you process exactly the amount of data you need.

VLOOKUP has the syntax =VLOOKUP(Look for this, In this range, Return the column, Flag) For example: Look for Black Walnut in the first column of Table_Pricing and give me what you find in the 5th column. =VLOOKUP(A3,'C:\Users\dflak\Temp\SUPPLYWORKBOOK.xlsx'!Table_Pricing[#Data],5,FALSE). The FALSE flag means give me an exact match. If you can't find an exact match return #N/A!.

If you move the table into the same workbook with the product, you won't have to have two workbooks open.