Vlookup item from middle area of a table

**prabhakarpeje** · 2013-10-22, 07:56 AM

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

**NBVC** · 2013-10-22, 03:33 PM

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)

**prabhakarpeje** · 2013-10-23, 09:55 AM

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.

**prabhakarpeje** · 2013-10-25, 08:18 AM

Originally Posted by NBVC

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..!

**NBVC** · 2013-10-25, 02:30 PM

Please supply a sample workbook showing your requirement.

**prabhakarpeje** · 2013-10-25, 03:19 PM

Originally Posted by NBVC

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

**prabhakarpeje** · 2013-10-25, 03:37 PM

Originally Posted by prabhakarpeje

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

**NBVC** · 2013-10-25, 07:17 PM

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

**prabhakarpeje** · 2013-10-26, 11:54 AM

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

Originally Posted by NBVC

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

**prabhakarpeje** · 2013-10-26, 01:45 PM

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....!