+ Reply to Thread
Results 1 to 4 of 4

using vlookup and I have a quantity that needs to be entered manually because it varies.

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    Waco, TX
    MS-Off Ver
    Excel 2003
    Posts
    2

    using vlookup and I have a quantity that needs to be entered manually because it varies.

    I'm making a form that fills in billing info for our products. I have a lookup table that has the products and their weights. My form uses this table to fill in the weight of the product after the user fills in the product. However, some products have varying weights and need to be entered manually. I would like to have an input box come up when the user types in a product that needs a custom weight input. After they enter the weight, the form will continue on to the next entry. I have include my spreadsheet so you can check it out.

    Thanks
    Casemill
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: using vlookup and I have a quantity that needs to be entered manually because it varie

    Hi,

    unfortunately I cannot find the form that is used in your description. Could you provide it?

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: using vlookup and I have a quantity that needs to be entered manually because it varie

    Your data range in sheet "Codes and Weights" the first number is 14092 and you are trying to look up the value 14088. In order to do so you must first add a number less than 14088 and then your data range in "Codes and Weights" must be sorted in ascending order.

    If the vlookup function don't find an exact match it will chose the nearest lower match i.e. code 43007 has a weight of 70 kg so you enter code 43006 vlookup will tell you the weight is 40

    Alf

  4. #4
    Registered User
    Join Date
    04-19-2013
    Location
    Waco, TX
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: using vlookup and I have a quantity that needs to be entered manually because it varie

    Thanks Alf and tehNexus, but what I am looking for is not quite what you have suggested. Maybe I didn't explain it clearly. At my company, we have products, chicken to be precise, and most of the codes we use for the cases have fixed weights. For example, if I use code "14088", my vlookup will return a value of 40 lbs for a 40lb box of chicken breast that is packed in a box and we have given it a code of "14088". However some of our products have variable weights that need to be input on an as needed basis. For example if I enter a code of "13256", I would like for the vlookup function to call an input box where I can fill in the correct weight such as "54.47" I haven't made a form yet, I'm trying to see if I can trigger a prompt when the vlookup does not find a weight associated with a code. The form is actually the spreadsheet page "bill info". I'm hoping that I type the code in the left column and the weight appears in the adjacent column, but if the code has no weight associated with it, I will get a promt to enter a weight and that weight will then be placed in the adjacent column, then I can sum all the weights. Maybe this clears my question up a little. I really am grateful for your help. Thanks,
    Casemill
    Attached Files Attached Files

+ 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