Forum Discussion

JUSTIN JENNINGS's avatar
JUSTIN JENNINGS
Copper Contributor
Jun 04, 2018

Referencing a value in a table

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 t...
Excel on Mac
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Jun 05, 2018

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

  • JUSTIN JENNINGS's avatar
    JUSTIN JENNINGS
    Copper Contributor
    Jun 12, 2018

    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.

    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP
      Jun 14, 2018

      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.

      • JUSTIN JENNINGS's avatar
        JUSTIN JENNINGS
        Copper Contributor
        Jun 20, 2018
        Thank you, Teylyn

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

        regards,
        Justin

Resources