Need help subtracting daily from a total inventory amount that is replenished every so often

[daltendavis]

My workbook is broken into 3 separate sheets, #1 I am entering in a daily amount of different bundles and or products which each have their own cell. This string of data is essentially copied to sheet #2 and deleted from sheet #1 as sheet #1 's only responsibility is the daily input. Sheet 2 serves as the "database" and is updated daily. Sheet # 3 is where I need help.

Sheet #3 serves as the inventory tracker and needs to be depleted as sheet # 2 is updated. The range where the daily data in sheet 2 starts at is E3:O3 and R3:AJ3. Each of those 30 cells has a cell on sheet 3 that needs to be depleted via formula/VBA/anything dependent on what is updated for the corresponding column on sheet 2. I will post an example of what my sheet 2 and 3 look like.

Sheet 2:

	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
2	Date	a	b	c	d	e	f	g	h	i	j			Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
3	2/5/2019	15	3	4	6	8	9	10	1	1	1			2/5/2019	0	0	1	3	6	0	0	1	4	8	0	0	0	1	1	1	6	9
4	2/6/2019	1	4	6	7	6	9	1	11	1	0			2/6/2019	0	0	0	0	0	0	1	4	6	8	9	7	5	2		4	2	3

<tbody>
</tbody>

Row 3/4 serve as examples of what the table will look like as it begins to generate. So again, for example row 3 column F, there would be 15 of product "a" going out. This would then be subtracted from a value on sheet 3 which follows:

A	B	C	D	E	F
1
2
3	Bundle:	Count		Product	Count
4	a	1000		1	1000
5	b	1000		2	1000
6	c	1000		3	1000
7	d	1000		4	1000
8	e	1000		5	1000
9	f	1000		6	1000
10	g	1000		7	1000
11	h	1000		8	1000
12	i	1000		9	1000
13	j	1000		10	1000
				11	1000
				12	1000
				13	1000
				14	1000
				15	1000
				16	1000
				17	1000

<tbody>
</tbody>

so back to the example before, because cell 3F on sheet 2 is 15 this would then subtract 15 from cell 4C on sheet 3.

Any help with this would be absolutely amazing, looking for a formula to plug into cells on sheet 3 to be able to do this if at all possible. Im very familiar with functions in excel and moderately familiar with VBA. Thank you in advance!

 

[RasGhul]

Hi,

Can you use this? Just adjust the ranges to suit your data;

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Date	a	b	c	d	e	f	g	h	i	j			Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	02-05-19	15	3	4	6	8	9	10	1	1	1			02-05-19	0	0	1	3	6	0	0	1	4	8	0	0	0	1	1	1	6	9
3	02-06-19	1	4	6	7	6	9	1	11	1	0			02-06-19	0	0	0	0	0	0	1	4	6	8	9	7	5	2		4	2	3
Sheet2

Book1
	A	B	C	D	E	F	G
1
2		Stock	Qty Available			Stock	Qty Available
3	Bundle:	Count			Product	Count
4	a	1000	984		1	1000	1000
5	b	1000	993		2	1000	1000
6	c	1000	990		3	1000	999
7	d	1000	987		4	1000	997
8	e	1000	986		5	1000	994
9	f	1000	982		6	1000	1000
10	g	1000	989		7	1000	999
11	h	1000	988		8	1000	995
12	i	1000	998		9	1000	990
13	j	1000	999		10	1000	984
14					11	1000	991
15					12	1000	993
16					13	1000	995
17					14	1000	997
18					15	1000	999
19					16	1000	995
20					17	1000	992
Sheet3

Cell Formulas
Range	Formula
C4	=B4-SUMPRODUCT((Sheet2!$F$1:$O$1=Sheet3!A4)*Sheet2!$F$2:$O$3)
G4	=F4-SUMPRODUCT((Sheet2!$S$1:$AJ$1=Sheet3!E4)*Sheet2!$S$2:$AJ$3)