Vlookup item from middle area of a table

prabhakarpeje

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
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..!


View attachment pharmac.xlsx
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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)
 

prabhakarpeje

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
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

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
Reply:

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

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Please supply a sample workbook showing your requirement.
 

prabhakarpeje

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
sample workbook showing your requirement.

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.

View attachment test.xlsx
 

prabhakarpeje

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
new file with changes

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.

View attachment 1780

New file with changes

View attachment test.xlsx
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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
 

prabhakarpeje

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
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...
View attachment test.xlsx





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

New member
Joined
Oct 22, 2013
Messages
7
Reaction score
0
Points
0
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....!
 
Top