Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup in Excel 2000 (Excel 2000 SP3)

    I am trying to develop a system of spreadsheets to modify recipes for our cafe. The primary sheet will contain our inventory that is used to prepare the recipes. Each subsequent sheet will be our recipes. In the area where I will list the incredients for the recipe, I want to be able to choose an ingredient from my list of PRODUCTS through a drop down within the individual cell (PRODUCT), and the next cells (UNIT OF ISSUE and UNIT PRICE) would automatically fill based on the PRODUCT chosen in the first cell. Is there an easy way to do this?

    Thanks in advance for any help or direction.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    Set up the primary sheet with the product in the first column, and the corresponding unit of issue and unit price in the same row further to the right.

    In the ingredients area of the recipe sheets, select the cells to contain the products. Then select Data | Validation...
    In the 'Allow' box, select List, then point to the column with products in the products sheet. In the second tab of the validation dialog, you can specify a hint to be displayed when the user enters one of the cells, and in the third tab, you can specify an error message to be displayed if the user tries to enter a product that is not in the list.
    In the cells to the right of this column, you can set up VLOOKUP formulas to retrieve the corresponding unit of issue and unit price. e.g.
    =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE)
    to look up a value in the second column.
    See <post#=402650>post 402650</post#> and my reply for methods to hide the #N/A error that is displayed when the user hasn't entered a product yet.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    Works great thanks for such a quick response. FYI though, while I was in data validation it would not let me reference the Product Sheet for the list (=Product!B9:Product!B100), so I went to the Product spreadsht and named the range "Products" and then put =Products as the range for the list validation. Did I do it incorrectly the first time?

    Thanks again

    shihalud

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    I'm sorry, I forgot to mention what you fortunately found out yourself. You cannot refer directly to a range on another worksheet, you must use a named range instead.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    Thanks so much for your help. But I have one last issue. I am having issues with the #NA as you mentioned, but the problem I am having is the in the total column. On a line where there has not yet been a product chosen and I have a formula in the Total Cost Column (G12*F12), and since both of those columns are #NA and I try to total this column, the total is #NA. Is there an IF statement that I can use to check for a null in the Product column, and if a product hasn't been chosen, leave the Total cell null, so that the column can be totaled with only those cells that have data? What I did was fill the cell with the word "Vacant", used conditional formatting as you suggested and changed font to background color if cell = "Vacant", and the formula in the Total column is: =If((R12="Vacant")," ",G12*F12). Is this the only solution or can I do this with just a formula in the Total column?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    You might use

    =IF(ISNA(VLOOKUP(...),0,VLOOKUP(...))

    for the lookup formulas. If you wish, you can hide 0 values with ordinary number formatting.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup in Excel 2000 (Excel 2000 SP3)

    Thanks again for the help - everything works great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •