+ Reply to Thread
Results 1 to 8 of 8

Help creating log with data from table

  1. #1
    Registered User
    Join Date
    02-15-2005
    Posts
    5

    Help creating log with data from table

    OK heres is what I am trying to accomplish. I have a worksheet (Nutrition Data) which contain a bunch of nutrional data (protein, carbs, fat, etc) on various food products (with unique names).

    What I would like is to create a new sheet (in the same workbook) that would allow me to lookup/enter the unique description of a food product (that is found in the "Nutritional Data" Sheet) then have the remaining data be automatically filled out in the adjacent column. So the end result is a multitude of rows which I can enter in my food products, the nutritional data would be autmatically pulled from the "Nutrional Data" sheet, and then the data can be totaled. I have been struggling with the VLOOKUP function with no success, perhaps there is a cleaner for effecient way. Maybe functions is not the way to go, maybe I need to go with the Excel VB.

    Any sample sheets that perform similarly to this, examples, tips, suggestions, etc?

  2. #2
    Registered User
    Join Date
    02-15-2005
    Posts
    5
    No assistance? Do I need more information?

  3. #3
    Forum Contributor
    Join Date
    05-03-2004
    Location
    England
    MS-Off Ver
    2003 Excel
    Posts
    118
    Hi Bryan,
    VLOOKUP should work.
    This is a very simple explanation (but I am simple) you may be able to use it to understand VLOOKUP.

    On sheet 1
    If A1:A3 contains your unique names
    B1:B3 contains fat content
    C1:C3 contains calories.

    On sheet 2
    in B1 put =VLOOKUP(A1,Sheet1!A1:C3,2)
    Then when you type the name you are looking for in A1 on sheet 2 it will search for that name in col A on sheet 1 and return the value found in column 2(B) for that row.

    in C1 put =VLOOKUP(A1,Sheet1!A1:C3,3)
    Then when you type the name you are looking for in A1 on sheet 2 it will search for that name in col A on sheet 1 and return the value found in column 3(C) for that row.

    When using VLOOKUP your unique names in column A in sheet 1 have to be in alphabetical order for this to work.



    Greg.

    You can expand this formula to suit your needs.

    There maybe a better way but I guess this is better than no reply.
    Last edited by greg7468; 02-17-2005 at 03:36 AM.

  4. #4
    Forum Contributor
    Join Date
    05-03-2004
    Location
    England
    MS-Off Ver
    2003 Excel
    Posts
    118
    Hi Bryan,
    Here is something else I have just learnt that maybe of use to you. Forgive me if I am trying to teach you the obvious.

    From the previous post if you put the formula in B1 on sheet 2 and drag the formula down this gives you 2 problems.

    It will not cell reference so the formula in B1 sheet 2 needs to be
    =VLOOKUP(A1,Sheet1!$A$1:$C$3,2)
    you can then drag B1 down to auto complete when you fill in A2, A3 etc in sheet 2.

    This will return a column of #NA where there is nothing in the corresponding cell in column A.

    If you put this formula in B1 and then drag it down it will leave the cell blank if nothing is entered into the cell in column A.

    =IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$C$3,2,FALSE))=TRUE,"",VLOOKUP(A1,Sheet1!$A$1:$C$3,2,FALSE))

    Hope this helps.

    Greg.
    Last edited by greg7468; 02-16-2005 at 12:46 PM.

  5. #5
    Registered User
    Join Date
    02-15-2005
    Posts
    5
    I am guessing the that simply fact that my "data" was not alphbetized was the reason for my strange errors.

    Thank you so much. That worked great, BUT, I would like to make better. The VLOOKUP is perfect for the nutrional data, but is there a way to have cell A1 on Sheet2, be a "selection list" or "drop-down" list of Column A in Sheet 1? That way the user will know exactly what options he has to pick from, rather than stabbing in the dark, hoping to hit one similar, or searching Sheet1 just to find a "food product"? I suspect this would require the VB Scipting. I have tinkered with one script that might do this, I'll have to experiment.

  6. #6
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    If I may interject here, Data Validation will give you the drop-down list you request. In cell A1 of Sheet2, from the main menu select Data>Validation

    On the "Settings" tab, choose 'List' in the "Allow" box and in the "Source" box, enter the range of your data on Sheet 1 (clicking the red arrow on the right end of the box will allow you to simply select your range).

    Click OK and you will now have a drop down box with your choices.

    May I recommend, for ease of use, that you simply place this Validation in each cell in Col A on Sheet2. That way, the user has the ease of simply selecting their data for each row. The VLOOKUP in Col. B will reference the value in Col. A and return the desired results.


    HTH
    Bruce
    The older I get, the better I used to be.
    USA

  7. #7
    Registered User
    Join Date
    02-15-2005
    Posts
    5
    swat, i see and understand exactly what you are talking about. That will accomplish exactly what I am after. Thank you gentlemen. Now lets see if I can make it happen.

  8. #8
    Registered User
    Join Date
    02-15-2005
    Posts
    5
    Drats! Excel 2002 will not allow me to reference data on another sheet for "validation criteria". Has anyone with a new version successfully referenced another worksheet with this feature.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1