What formula is able to solve my inventory management

[iMailMan]

Before withdraw on inventory monitoring page:
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Start Stock Qty[/TD]
[TD="align: center"]In Stock Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]456[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]789[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]


Withdrawing on inventory withdrawal page(different tab):

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date Withdraw[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Withdraw Qty[/TD]
[TD="align: center"]In Stock Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]05 Sep[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]06 Sep[/TD]
[TD="align: center"]456[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]07 Sep[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

What formula should use for cell E2 to E4?

After withdraw on inventory monitoring page:
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Start Stock Qty[/TD]
[TD="align: center"]In Stock Qty[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]456[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]789[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

What formula should use for cell D2 to D5?

I already crack my head how to link and share the in stock balance as above different tab shown.
Please help and educate me.


Thanks.

 

[RasGhul]

Hi Mailman,

Maybe this will work for you. Drag the formula down on the Inventory tab. I recommend converting the Withdrawals ledger into a table so then as you add lines it will automatically update your inventory formula range;

Book1
	A	B	C	D
1	Part Number	Start Stock Qty	Stocktake Count	Withdrawals
2	123	10	8	3
3	456	20	7	6
4	789	30	6	2
5	101	40	5	1
Inventory

Cell Formulas
Range	Formula
D2	=SUMPRODUCT((Table1[Part Number]=Inventory!A2)*Table1[Withdraw Qty])

Book1
	A	B	C	D
1	Date Withdraw	Part Number	Withdraw Qty	In Stock Balance
2	05-Sep	123	2	5
3	06-Sep	456	3	1
4	07-Sep	123	1	5
5	08-Sep	456	1	1
6	09-Sep	789	2	4
7	10-Sep	101	1	4
8	11-Sep	456	1	1
9	12-Sep	456	1	1
Withdrawals

Cell Formulas
Range	Formula
D2	=VLOOKUP(B2,Inventory!$A$2:$D$5,3,0)-VLOOKUP(B2,Inventory!$A$2:$D$5,4,0)

 

[jtakw]

Hi,

Not entirely certain I understand all your requirements, may be this:

Book1
	A	B	C	D
1	1	Part Number	Start Stock Qty	In Stock Balance
2	2	123	10	5
3	3	456	20	15
4	4	789	30	29
5	5	101	40	31
Monitoring

Cell Formulas
Range	Formula
D2	=C2-SUMIF(Withdrawal!C$2:C$8,B2,Withdrawal!D$2:D$8)

Book1
	A	B	C	D	E
1	1	Date Withdraw	Part Number	Withdraw Qty	In Stock Balance
2	2	5-Sep	123	2	8
3	3	6-Sep	456	3	17
4	4	7-Sep	123	1	7
5	5	8-Sep	123	2	5
6	6	9-Sep	456	2	15
7	7	10-Sep	101	9	31
8	8	11-Sep	789	1	29
Withdrawal

Cell Formulas
Range	Formula
E2	=SUMIF(Monitoring!B$2:B$5,C2,Monitoring!C$2:C$5)-SUMIF(C$2:C2,C2,D$2:D2)

Change/adjust cell references and Sheet Name to suit your data, formulas copied down.

 

[iMailMan]

Dear All,

Thanks for your formula and it was work for me.
The only problem i have faced is how to combine different tab into the formula?

 

[RasGhul]

List your tabs and specify what data is in each if that differs from your original query. If you have more than one tab for withdrawals then we may need to update our formula.

 

[iMailMan]

Inventory Page A (Sheet 1)
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Start Stock Qty[/TD]
[TD="align: center"]In stock Balance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]A123[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A456[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]A789[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A101[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]


Inventory Page B (Sheet 2)
[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Start Stock Qty[/TD]
[TD="align: center"]In Stock Balance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]B123[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]B456[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]B789[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]B101[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]


How to combine above two inventory page at cell E2:E4 under withdrawal page (Sheet 3)?

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date Withdraw[/TD]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Withdraw Qty[/TD]
[TD="align: center"]In stock Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10 sep[/TD]
[TD="align: center"]A123[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]11 Sep[/TD]
[TD="align: center"]B789[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12 Sep[/TD]
[TD="align: center"]A123[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[iMailMan]

Have any expertise can help me with above mentioned?

 

[RasGhul]

Hi iMail,

You will need to extend these ranges to suit or it would be better to convert your Withdrawals table to a table so you don't have to keep expanding your formula ranges as your data increases;

Book1
	A	B	C
1	Part Number	Start Stock Qty	In stock Balance
2	A123	10	7
3	A456	20	20
4	A789	30	30
5	A101	40	40
InventoryA

Cell Formulas
Range	Formula
C2	=B2-SUMIF(Withdrawals!$B$2:$B$4,InventoryA!A2,Withdrawals!$C$2:$C$4)

Book1
	A	B	C
1	Part Number	Start Stock Qty	In Stock Balance
2	B123	10	10
3	B456	20	20
4	B789	30	27
5	B101	40	40
InventoryB

Cell Formulas
Range	Formula
C2	=B2-SUMIF(Withdrawals!$B$2:$B$4,InventoryB!A2,Withdrawals!$C$2:$C$4)

Book1
	A	B	C	D
1	Date Withdraw	Part Number	Withdraw Qty	In stock Balance
2	10-Sep	A123	2	7
3	11-Sep	B789	3	27
4	12-Sep	A123	1	7
Withdrawals

Cell Formulas
Range	Formula
D2	=IF(SUMIF(InventoryA!$A$2:$A$5,Withdrawals!B2,InventoryA!$B$2:$B$5)>0,SUMIF(InventoryA!$A$2:$A$5,Withdrawals!B2,InventoryA!$B$2:$B$5),SUMIF(InventoryB!$A$2:$A$5,Withdrawals!B2,InventoryB!$B$2:$B$5))-SUMIFS($C$2:$C$5,$B$2:$B$5,B2)

 

[RasGhul]

Just in case the screen has cut down the formula;

=IF(SUMIF(InventoryA!$A$2:$A$5,Withdrawals!B2,InventoryA!$B$2:$B$5)>0,SUMIF(InventoryA!$A$2:$A$5,Withdrawals!B2,InventoryA!$B$2:$B$5),SUMIF(InventoryB!$A$2:$A$5,Withdrawals!B2,InventoryB!$B$2:$B$5))-SUMIFS($C$2:$C$5,$B$2:$B$5,B2)

 

[jtakw]

Dear All,

Thanks for your formula and it was work for me.
The only problem i have faced is how to combine different tab into the formula?

As RasGhul's formulas in Post # 2 and my formulas in Post # 3 provide completely different results, I had No idea which of the 2 different solutions you were referring to, thus I did not respond.

Looks like RasGhul has modified my formulas for your additional requirements, I have not looked at or tested them, hope it all works out for you, if you have additional questions, please post back and be specific on How you want your results.