Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hello,
I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hi
assuming that all data is entered as positive values, and with A1 =Opening stock B1 =Shipped Out C1 =Shipped In D1 =Current Stock In D2 =A1-B1+C1 -- Regards Roger Govier "vandy" wrote in message ... Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hello Roger,
The current stock does not show the correct value after 3 to 4 entries of out and in. Does D2 hold the current stock now. for eg. Current stock is 100 A B C D opening stock in out current stock 100 80 0 180 0 20 160 40 0 140 I GET THIS RESULT WHEN I ENTER THE VALUE IN AS 40. I SHOULD BE GETTING 160+40 = 200 AS AGAINST 140 SINCE ITS ADDING THE CURRENT VALUE OF OPENING STOCK 100. The current stock has to keep the updated value of opening stock after every transaction i dont believe this is happening. KINDLY ADVISE . THANKS A TON. VANDY shipped out "Roger Govier" wrote: Hi assuming that all data is entered as positive values, and with A1 =Opening stock B1 =Shipped Out C1 =Shipped In D1 =Current Stock In D2 =A1-B1+C1 -- Regards Roger Govier "vandy" wrote in message ... Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
In D2 =A2+B2-C2
In D3 =IF(AND(B3="",C3=""),"",D2+B3-C3) Copy from D3 down as far as needed "vandy" wrote: Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hi
I made a hash of that didn't I!!! D2 should have been =IF(COUNT(B2:C2)<1,"",A2+B2-C2) A3 should be =D2 D3 should be =IF(COUNT(B3:C3)<1,"",A3+B3-C3) Copy A3:D3 down the page as far as required -- Regards Roger Govier "vandy" wrote in message ... Hello Roger, The current stock does not show the correct value after 3 to 4 entries of out and in. Does D2 hold the current stock now. for eg. Current stock is 100 A B C D opening stock in out current stock 100 80 0 180 0 20 160 40 0 140 I GET THIS RESULT WHEN I ENTER THE VALUE IN AS 40. I SHOULD BE GETTING 160+40 = 200 AS AGAINST 140 SINCE ITS ADDING THE CURRENT VALUE OF OPENING STOCK 100. The current stock has to keep the updated value of opening stock after every transaction i dont believe this is happening. KINDLY ADVISE . THANKS A TON. VANDY shipped out "Roger Govier" wrote: Hi assuming that all data is entered as positive values, and with A1 =Opening stock B1 =Shipped Out C1 =Shipped In D1 =Current Stock In D2 =A1-B1+C1 -- Regards Roger Govier "vandy" wrote in message ... Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Thank you so much for your replies . They are great but I still have a
problem. The formula works perfectly if there is only one item and we want to increase and decrease the stock value but the current scenario is: A B C D E item opening stock shipped out shipped in current stock item a 100 20 0 80 2nd transaction 0 40 120 item b item c item d item e I have ONLY CELL C3 AND D3 TO INPUT DATA AND I THINK E3 should keep updating itself to reflect the data for c3 and d3. I have to do in a single line each time and the result to be reflected in current stock. I have to do this for each of the other items. Am i complicating things or is there any easy way to go about this. thanks a ton and I appreciate your help answers.any advise. vandy "vandy" wrote: Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hi All,
Can anyone help me out with my query. i havent been able to decrease and increase my stock qty using the formulas given. It works perfectly for a single line item but i hav got more than 20 items per sheet and wanted to just add the shipped out and shipped in qty and have the current stock change. I think i have to use circular reference can any one help me out. thanks in advance vandy "vandy" wrote: Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INVENTORY STOCK COUNT
Hi
If you do away with having entries in your opening stock column, and just make then entries in Shipping in, and if you list your 20 product names in cells E1:X1 then in cell E2 =SUMPRODUCT(($A$2:$A$1000=E$1)*($D$2:$D$100)) -SUMPRODUCT(($A$2:$A$1000=E$1)*($C$2:$C$1000)) and copy across through F2:X2 -- Regards Roger Govier "vandy" wrote in message ... Hi All, Can anyone help me out with my query. i havent been able to decrease and increase my stock qty using the formulas given. It works perfectly for a single line item but i hav got more than 20 items per sheet and wanted to just add the shipped out and shipped in qty and have the current stock change. I think i have to use circular reference can any one help me out. thanks in advance vandy "vandy" wrote: Hello, I am trying to calculate the stock inventory of items in my company. I have imported data from access into excel and attemting to calculate the stock increase and decrease value. Eg CURRENT STOCK = 200 ITEM SHIPPED OUT = 20 CURRENT STOCK = 220 ITEM SHIPPED IN = 100 CURRENT STOCK = 120 It should change according to the qty shipped our or shipped in and should show current inventory stock for each item in excel. Can anyone please help with the formula to be used for the same. thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do update inventory | Excel Discussion (Misc queries) | |||
Automatically Assign Stock Number By Model | Excel Discussion (Misc queries) | |||
A calculator for inventory safety stock | Excel Discussion (Misc queries) | |||
Excel Stock List | Excel Discussion (Misc queries) | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions |