Stock In and Stock Out Balance per transaction in excel

[Peter888]

Hi, i want to know if it is possible to make detailed inventory stock balance per transaction between date i choose rather than monthly. It keeps tracking how many specific items i have left per that date. I made transaction history tracker which act as date filter on my excel. Can someone give me some idea on how to make it possible to track each transaction end balance? I made a In, Out, Inventory List Sheet. In sheet had a table with all stock in transaction, Out sheet had a table with all stock out transaction and Inventory list had beginning stock amount and name.

GE]

 

[Peter888]

Customer Sheet

Stock Inventory.xlsm
	C
3	Customer Name
4	Jack
5	Tom
6	Tony
7	Mia
8	Allen
9	Eric
10	David
11	Mark
Customer List

Inventory Name and Beginning Stock List

Code Name	Product Name	Stock	In	Out	Balance	Unit Name
Apple 1	Fuji Apple	1,000	-	100	+[@In]-[@Out]]900	Pcs
Apple 2	Wanshang Apple	500	40	50	+[@In]-[@Out]]490	Pcs
Orange 1	Navel Orange	200	-	-	+[@In]-[@Out]]200	Pcs
Orange 2	Valencia Orange	150	-	-	+[@In]-[@Out]]150	Pcs

Stock In Transaction List

Stock Inventory.xlsm
	E	F	G	H			K
3	Delivery ID	Item Name	Product Name	QTY			Description
4	01-Jan-00	Apple 2	Wanshang Apple	40			Purchased from Greenfield Farm
In database

Cell Formulas
Range	Formula
G4	G4	=IFERROR(VLOOKUP($F4,Table4[[Code Name]:[Unit Name]],2,0),"")

Named Ranges
Name	Refers To	Cells
In	=SalesData4[Item Name]	G4

Cells with Data Validation
Cell	Allow	Criteria
F4	List	=NomorKodeBarang

Stock Out Transaction List

Stock Inventory.xlsm
	D	E	F	G	H	I	J	K
3	Date	Delivery Order ID	Customer Name	Item Code	Item Name	QTY	Description	ListDoc
4	11-Okt-21	1	Mia	Apple 1	Fuji Apple	100	Payment by cash	1
5	11-Okt-21	2	Jack	Apple 2	Wanshang Apple	50	Payment by credit	2
Out database

Cell Formulas
Range	Formula
H4:H5	H4	=IFERROR(VLOOKUP($G4,Table4[[Code Name]:[Unit Name]],2,0),"")
K4	K4	=MIN([Delivery Order ID])
K5	K5	=IFERROR(IF(K4+1<$K$4+COUNTIF([Line'#],1),K4+1,""),"")

Named Ranges
Name	Refers To	Cells
Out	=SalesData[Item Code]	H4

Cells with Data Validation
Cell	Allow	Criteria
F4:F5	List	=NamaPerusahaan
G4:G5	List	=NomorKodeBarang

Inventory Report

Stock Inventory.xlsm
	A	B				F	G	H
1	ABC Company
2	Inventory Balance
3
4	Code	Apple 2				Period
5	Nama :	Wanshang Apple				Begin	30/09/21
6						End	31/11/2021
7
8	No	Tanggal				Inventory
9						In	Out	Total
10	1	30-Sep-21				-	-	#VALUE!
11	2	1-Oct-21				-	-	#VALUE!
12	3	2-Oct-21				-	-	#VALUE!
13	4	3-Oct-21				-	-	#VALUE!
14	5	4-Oct-21				-	-	#VALUE!
15	6	5-Oct-21				-	-	#VALUE!
16	7	6-Oct-21				-	-	#VALUE!
17	8	7-Oct-21				-	-	#VALUE!
18	9	8-Oct-21				40	-	#VALUE!
19	10	9-Oct-21				-	-	#VALUE!
20	11	10-Oct-21				-	-	#VALUE!
21	12	11-Oct-21				-	50	#VALUE!
22	13	12-Oct-21				-	-	#VALUE!
23	14	13-Oct-21				-	-	#VALUE!
24	15	14-Oct-21				-	-	#VALUE!
25	16	15-Oct-21				-	-	#VALUE!
26	17	16-Oct-21				-	-	#VALUE!
27	18	17-Oct-21				-	-	#VALUE!
28	19	18-Oct-21				-	-	#VALUE!
29	20	19-Oct-21				-	-	#VALUE!
30	21	20-Oct-21				-	-	#VALUE!
31	22	21-Oct-21				-	-	#VALUE!
32	23	22-Oct-21				-	-	#VALUE!
33	24	23-Oct-21				-	-	#VALUE!
34	25	24-Oct-21				-	-	#VALUE!
35	26	25-Oct-21				-	-	#VALUE!
36	27	26-Oct-21				-	-	#VALUE!
37	28	27-Oct-21				-	-	#VALUE!
38	29	28-Oct-21				-	-	#VALUE!
39	30	29-Oct-21				-	-	#VALUE!
40	31	30-Oct-21				-	-	#VALUE!
41	32	31-Oct-21				-	-	#VALUE!
42	33	1-Nov-21				-	-	#VALUE!
Inventory Report

Cell Formulas
Range	Formula
B5	B5	=IFERROR(VLOOKUP($B4,Table4[[Code Name]:[Unit Name]],2,0),"")
F10:F42	F10	=SUMIFS(InQTY,In,'Inventory Report'!$B$4,DateIn,'Inventory Report'!B10)
G10:G42	G10	=SUMIFS(QTY,Out,'Inventory Report'!$B$4,Date,'Inventory Report'!B10)
H10	H10	=Table4[Stock]+F10-G10
H11:H42	H11	=H10+F11-G11
B10	B10	='Inventory Report'!G5
B11:B42	B11	=IF(B10<'Inventory Report'!$G$6,'Inventory Report'!B10+1,"")

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=NomorKodeBarang

 

[Peter888]

I need someone to give me idea how to fix total balance at Inventory Report based on specific date