Using "MONTH" with Defined Names Returning Results 2 Months Off

[SewStage]

I have the following formula used to display rolling inventory totals each month (replicated across row 2), where PPIVITEM and PPIVCOUNT are Defined Names from a pivot table (tab) used to add inventory purchases each month. As you can see from both the purchase entry and the pivot table, 5 animal eyes were added on 10/1, but it's being reflected two months earlier in August on the Material by Mo. tab; this is true for any item that has been added and any purchase date that is used. If I spell out the entire formula instead of using Defined Names it works just fine. Thoughts on why this is happening/what may be wrong in my Defined Names? Thanks much.

2021 Inventory Management.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item Description	UOM	31-Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Animal eyes (sets)	set	38	38	38	38	38	38	38	38	43	43	43
Material by Mo.

Cell Formulas
Range	Formula
D2:O2	D2	=IF(MONTH(D1)<=MONTH(TODAY()),C2+SUMIF(PPIVITEM,$A$2,PPIVCOUNT),"")

Pivot table:

2021 Inventory Management.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
3	Sum of Units
4		1	2	3	4	5	6	7	8	9	10	11	12	Grand Total
5	Animal eyes (sets)										5			5
Purchases pivot table

Purchases (tab):

2021 Inventory Management.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Category	Description	Details	Cost	Units	Per Unit Cost	Source	Date of Purchase	Online	Pay Source	Mo of Purchase	Yr of Purchase
46	Notions	Animal eyes (sets)		$ 10.00	5	$ 2.00	Amazon	10/1/2021	X		10	2021
Material purchases

Cell Formulas
Range	Formula
F46	F46	=IF(E46>0,D46/E46,"")
K46	K46	=MONTH((H46))

Defined Names:

Results without using Defined Names:

2021 Inventory Management.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item Description	UOM	31-Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Animal eyes (sets)	set	38	38	38	38	38	38	38	38	38	38	43
Material by Mo.

Cell Formulas
Range	Formula
D2:O2	D2	=IF(MONTH(D1)<=MONTH(TODAY()),C2+SUMIF('Purchases pivot table'!$A$5:$A$150,'Material by Mo.'!$A$2,'Purchases pivot table'!B$5:B$150),"")

 

[Alex Blakenburg]

OK Try this.
In the materials sheet select Cell D2 (Jan),
go into the Name Manager
select PPIVCOUNT and hit edit
You should find it currently says
='Purchases Pivot Table'!C$2:C$150
Change this to (change C to B)
='Purchases Pivot Table'!B$2:B$150
Save the change.

Does that fix the issue ?

 

[SewStage]

OK Try this.
In the materials sheet select Cell D2 (Jan),
go into the Name Manager
select PPIVCOUNT and hit edit
You should find it currently says
='Purchases Pivot Table'!C$2:C$150
Change this to (change C to B)
='Purchases Pivot Table'!B$2:B$150
Save the change.

Does that fix the issue ?

I gotta say, I'm embarrassed I didn't catch that! Guess that's why a second set of eyes is always good. Thanks so much, Alex; very much appreciated!

 

[Alex Blakenburg]

No problem. I don't think a misalignment in the relative addressing of a named range is actually that obvious. Happy to have been able to provide the second set of eyes.