Forum Discussion
Referencing a value in a table
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
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.
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.
- Thank you, Teylyn
I will try and implement this and see if i can understand how it works
regards,
Justin