Results 1 to 10 of 10

Thread: Vlookup item from middle area of a table

  1. #1

    Vlookup item from middle area of a table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I am currently working in a hospital and i am thinking of preparing an excel file for pharmacy staff where a stock, inventory & bills would be maintained in a single file. i have a stock list and a bill format in a single sheet here i need a help for the following things:
    1. In bill format i want to add a drug/material name using data validation list and then by using vlookup function i need automatically pull the rate from the stock list of the sideby specified drug/material name(added by data validation).
    2. then i need to auto deduct the qty specified in bill format's qty column from the stock list of the stock qty column.

    i have tried hard to make but was not successfull so i have attached the file for ur editing..!

    Thanking you in advance for the help..!


    pharmac.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    For your VLOOKUP formula the 2nd parameter (the lookup table) should start with the column that contains the information you are looking up (Column G, in this case), then count the columns to the right of that (including column G) to get the column index number to pull rate from.

    =IF(Q11="","",VLOOKUP(Q11,$G$2:$N$1422,7,FALSE))

    Note: I added the IF() statement so you don't show errors when the product is not yet chosen.

    Similarly, the Amount column uses the IF():

    =IF(Q11="","",V11*U11)


  3. #3
    Thank you Very Much for the Help & great to learn more new things.

    Thanks once again , i got the help for the first problem but i also want to auto deduct the quantity given in the bill of the specific product from my main stock list so that i can get an updated stock every time i make a new bill. Another thing can there be sum provision to get a message or sum red marking cell to know that the stock have gone below the reorder level specified.

  4. #4

    Unhappy Reply:

    Quote Originally Posted by NBVC View Post
    For your VLOOKUP formula the 2nd parameter (the lookup table) should start with the column that contains the information you are looking up (Column G, in this case), then count the columns to the right of that (including column G) to get the column index number to pull rate from.

    =IF(Q11="","",VLOOKUP(Q11,$G$2:$N$1422,7,FALSE))

    Note: I added the IF() statement so you don't show errors when the product is not yet chosen.

    Similarly, the Amount column uses the IF():

    =IF(Q11="","",V11*U11)

    Dear NBVC please reply my another help..!

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Please supply a sample workbook showing your requirement.


  6. #6

    sample workbook showing your requirement.

    Quote Originally Posted by NBVC View Post
    Please supply a sample workbook showing your requirement.

    Here in this file when i enter the quantity in bill format i want to automatically deduct the QTY(In Green Colour) from the Quantity coloumn(In Blue Colour) in sideby Main Stock list of the specified product name/item. Note : the deduction should be done like = Quantity Column(In main stock list) -minus Qty Column (in the bill) of the specified product/item.

    test.xlsx

  7. #7

    new file with changes

    Quote Originally Posted by prabhakarpeje View Post
    Here in this file when i enter the quantity in bill format i want to automatically deduct the QTY(In Green Colour) from the Quantity coloumn(In Blue Colour) in sideby Main Stock list of the specified product name/item. Note : the deduction should be done like = Quantity Column(In main stock list) -minus Qty Column (in the bill) of the specified product/item.

    test.xlsx
    New file with changes

    test.xlsx

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I am not sure what you want can be done with formulas only. You may need VBA (which will require someone with better VBA skills to join in to assist you).

    One way to avoid VBA is to add another column which contains the "Qty after deduction"... so it would contain a formula like: =I2-VLOOKUP(G2,$Q$10:$U$30,5,FALSE) copied down


  9. #9

    Reply..:

    Dear NBVC, Thanking you once again, i inserted the formula it is working fine but the another previous =IF(R10="","",VLOOKUP(R10,vtable,7,FALSE)) and =IF(R10="","",W10*V10) you gave are not working please have a look at it i am attaching the file again...
    test.xlsx





    Quote Originally Posted by NBVC View Post
    I am not sure what you want can be done with formulas only. You may need VBA (which will require someone with better VBA skills to join in to assist you).

    One way to avoid VBA is to add another column which contains the "Qty after deduction"... so it would contain a formula like: =I2-VLOOKUP(G2,$Q$10:$U$30,5,FALSE) copied down

  10. #10
    hi SORRY !

    i got the problem error it was the column number was not changed when i inserted a column. now every things fine I will coppy the bill format on second sheet2 and again re-edit the bill on sheet1 but then when i change the bill items and qty, the qty on main stock list again goes on its original value. Actually how i want is when i make bill no 1 the qty in stock list will get deducted and then again when i reedit the bill for bill no 2 its should deduct the qty from the deducted qty. means the whenever i deduct a qty for every bill the stock qty should get reduced i.e. 100-5=95, then 95-5=90,then 90-5=85, so on....!

Posting Permissions

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