|
Inventory calculations
Hi All,
I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
in qoh, why not put a formula
=Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
Hello Tom,
Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
In your example, when you issued 4, you changed the 56 to a 54 and the 2 to
a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
I am sorry. I did not ask the question in the proper order.
Once the item is received as instockt the value does not change. The items gets issued as per requirments and the QOH has to be calculated for every issue made. How to calculate the qoh and simultaneously update the instock amount to reflect the qoh to keep it current. item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i issue item no Instock Issued Qoh aa 56 3 53 bb 30 2 28 my qoh for the items should be aa 54-3 = 51 bb 29-2 = 27 how can i do this. thanks for your patience in reading this msg again! "Tom Ogilvy" wrote: In your example, when you issued 4, you changed the 56 to a 54 and the 2 to a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
I'm sorry. I left out something.
You can't get the resulting QOH with the vlookup. You should create a pivot table to get the total quantites issued, and then use a vlookup to tie that back to your Items range by subtracting the vlookup result from the beginning quantity. Ross "vandy" wrote in message ... I am sorry. I did not ask the question in the proper order. Once the item is received as instockt the value does not change. The items gets issued as per requirments and the QOH has to be calculated for every issue made. How to calculate the qoh and simultaneously update the instock amount to reflect the qoh to keep it current. item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i issue item no Instock Issued Qoh aa 56 3 53 bb 30 2 28 my qoh for the items should be aa 54-3 = 51 bb 29-2 = 27 how can i do this. thanks for your patience in reading this msg again! "Tom Ogilvy" wrote: In your example, when you issued 4, you changed the 56 to a 54 and the 2 to a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
Looks like this, though you can make it alot neater.
Item Beginning QOH Item Issued Sum of Issued AA 50 43 AA 4 Item Total BB 30 26 BB 3 AA 7 CC 25 19 AA 1 BB 4 CC 6 CC 6 AA 2 Grand Total 17 BB 1 "Ross Culver" wrote in message ... Vandy, You need to separate your transaction recording from your current status. And, by the way, you're confusing your terminology. Instock and QOH are the same thing. Set a range that indicates the beginning quantities: item No Beginning Qty aa 56 bb 30 cc etc. Keep your transaction range as shown below: Item No Issued aa 2 bb 1 aa 3 bb 2 Then in a separate range you need (you could combine this column with the beginning quantities range to save space): Item No Beginning Qty QOH aa 56 here you need a vlookup to get the total qty issued and then subtract this number from beginning qty bb 30 cc etc. If you're not familiar with vlookups, let me know. Ross "vandy" wrote in message ... I am sorry. I did not ask the question in the proper order. Once the item is received as instockt the value does not change. The items gets issued as per requirments and the QOH has to be calculated for every issue made. How to calculate the qoh and simultaneously update the instock amount to reflect the qoh to keep it current. item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i issue item no Instock Issued Qoh aa 56 3 53 bb 30 2 28 my qoh for the items should be aa 54-3 = 51 bb 29-2 = 27 how can i do this. thanks for your patience in reading this msg again! "Tom Ogilvy" wrote: In your example, when you issued 4, you changed the 56 to a 54 and the 2 to a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
Hello Ross,
Thanks for answering my question. I am not very familiar with Excel i could manage this with Access but a lot of people are familiar with Exel and so i need to automate the QOH via excel. You are right about the qoh and instock value. I am not familiar with vlookup so please do help me out on that. Also when you say set a range do you mean use a space range say a1 .. d30 on a worksheet or use another worksheet and link the data. I am sorry if these are really basic questions but i wish to be able to understand the process. to set my transaction range does it mean typing the item no each time and entering the issued qty. Item No Issued aa 2 bb 1 aa 3 bb 2 thanks again for your inputs. "Ross Culver" wrote: Vandy, You need to separate your transaction recording from your current status. And, by the way, you're confusing your terminology. Instock and QOH are the same thing. Set a range that indicates the beginning quantities: item No Beginning Qty aa 56 bb 30 cc etc. Keep your transaction range as shown below: Item No Issued aa 2 bb 1 aa 3 bb 2 Then in a separate range you need (you could combine this column with the beginning quantities range to save space): Item No Beginning Qty QOH aa 56 here you need a vlookup to get the total qty issued and then subtract this number from beginning qty bb 30 cc etc. If you're not familiar with vlookups, let me know. Ross "vandy" wrote in message ... I am sorry. I did not ask the question in the proper order. Once the item is received as instockt the value does not change. The items gets issued as per requirments and the QOH has to be calculated for every issue made. How to calculate the qoh and simultaneously update the instock amount to reflect the qoh to keep it current. item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i issue item no Instock Issued Qoh aa 56 3 53 bb 30 2 28 my qoh for the items should be aa 54-3 = 51 bb 29-2 = 27 how can i do this. thanks for your patience in reading this msg again! "Tom Ogilvy" wrote: In your example, when you issued 4, you changed the 56 to a 54 and the 2 to a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
Inventory calculations
This really looks like crap after it gets to the forum. If you like, send
me an email and I'll send you a sample. Ross "Ross Culver" wrote in message ... Looks like this, though you can make it alot neater. Item Beginning QOH Item Issued Sum of Issued AA 50 43 AA 4 Item Total BB 30 26 BB 3 AA 7 CC 25 19 AA 1 BB 4 CC 6 CC 6 AA 2 Grand Total 17 BB 1 "Ross Culver" wrote in message ... Vandy, You need to separate your transaction recording from your current status. And, by the way, you're confusing your terminology. Instock and QOH are the same thing. Set a range that indicates the beginning quantities: item No Beginning Qty aa 56 bb 30 cc etc. Keep your transaction range as shown below: Item No Issued aa 2 bb 1 aa 3 bb 2 Then in a separate range you need (you could combine this column with the beginning quantities range to save space): Item No Beginning Qty QOH aa 56 here you need a vlookup to get the total qty issued and then subtract this number from beginning qty bb 30 cc etc. If you're not familiar with vlookups, let me know. Ross "vandy" wrote in message ... I am sorry. I did not ask the question in the proper order. Once the item is received as instockt the value does not change. The items gets issued as per requirments and the QOH has to be calculated for every issue made. How to calculate the qoh and simultaneously update the instock amount to reflect the qoh to keep it current. item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i issue item no Instock Issued Qoh aa 56 3 53 bb 30 2 28 my qoh for the items should be aa 54-3 = 51 bb 29-2 = 27 how can i do this. thanks for your patience in reading this msg again! "Tom Ogilvy" wrote: In your example, when you issued 4, you changed the 56 to a 54 and the 2 to a 4. Then the formula would show 50 which is correct I believe. -- Regards, Tom Ogilvy "vandy" wrote: Hello Tom, Thanks for your reply. that is the first thing i tried. b10 c10 d10 instock issue qoh = instock - issue 56 2 54 next time when i go to issue and change it to 4 it will give me 56-4 = 52 which is wrong. Should not the qoh = instock qty to make the qoh current. or am i missing something here. "Tom Ogilvy" wrote: in qoh, why not put a formula =Instock-issue so if instock was B10 and issue was C10, D10: =B10-C10 -- Regards, Tom Ogilvy "vandy" wrote: Hi All, I have a situation were i need to calculate the current stock inventory of items. eg: item no Instock Issued Qoh aa 56 2 54 bb 30 1 29 next time when i go to item aa and bb and issue some more items than it should update the qoh eg qoh. should get updated as instock for the next transaction. item no Instock Issued Qoh aa 54 4 40 bb 29 2 27 can this be done. I am not very good with VBA. Any help would be appreciated. thanks in advance |
| All times are GMT +1. The time now is 12:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com