Referencing a value in a table

[JUSTIN JENNINGS]

I have data in a table with multiple (different suppliers prices) item cost values in the rows beside each item name.  In another table i wish create a stocktake sheet to calculate inventory, to do this i wish to reference only 1 of the cost values from each item.  

My problem is that when i organise my original table be ascending/descending order when adding adding additional inventory, the value in the stocktake sheet no longer matches the item value first referenced.

Is it possible to create a reference that will follow a value when a table is organised in this way?

[#2]

Hello,

Can you post a simplified sample in an Excel workbook and explain in context? It would be good to see how you reference the inventory.

I'm sure this can be done with a lookup formula, but  in order to suggest a formula we'd need to know how the data is organised.

cheers, teylyn

[#3]

This is a stocktake list used to count the value of stock on hand. The highlighted cell references a specific cell on the other worksheet within the same workbook.This is my inventory sheet with prices from multiple suppliers.

Thank you Teylyn,

I have attached some screen shots, if this can help. There is no sensitive information within the sheets as i am in the setup stages so have attached them also.

The problem i have is when i change the order of the inventory sheet (comparison sheet) alphabetically via the ascending/descending tabs at the top, the value that i wish to reference also changes. I wish to create a reference that can follow this value that has been referenced.

The current reference that i have been using is visible in the top screenshot.

The values in the inventory sheet (comparison sheet) have been entered directly and not referenced from another source.

[#4]

Hello,

you are using references to fixed cells and they are not always from the same column in the comparison sheet. Also, some of the prices are multiplied. It is really difficult to tie this into a system.

I suggest that you create a single list of all products and their prices in the comparison sheet. Right now you have several different tables and that makes a formula solution quite difficult. 

With a single lookup table, you can use a Vlookup in the stocktake sheet and look up the product by its name and return the price. For example, if a new column with the price is in column G of the comparison sheet, 

=VLOOKUP(A2,'comparison sheet'!$D$1:$L$56,4,FALSE)

But a lookup formula will need the same product text in the stocktake sheet and the comparison sheet. It will not work if the product is called "chicken breast" in the stocktake sheet and "chicken breast supreme (skin wing on)" in the comparison sheet.

[#5]

Thank you, Teylyn

I will try and implement this and see if i can understand how it works

regards,
Justin