Trying to dissect circular references...

[kyddrivers]

I have three large complex workbooks that do some heavy lifting. I am now getting an error when calculating: Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command.

When I look at error checking Circular reference is greyed out.

The lower left-hand corner says circular references: XXX, sometimes pointing to an empty cell.

I do not have enable iterative calculations checked.

Here is a simple example:

Circular reference in A258. The formula in A258 is =EOMONTH(TODAY(),9)+1. This returns 3/1/2024. This is working as expected.

Here is a complex example that I am trying to understand:

Circular reference in AB19. The formula in AB19 is: =XLOOKUP(MAX(Calls!$H$162:$H$165), Calls!$H$162:$H$165, Calls!$G$162:$G$165). This returns the date from CallsH162:165 with the max number in CallsG162:165

The formula in Calls!$H$162 is =SUM(OFFSET(INDEX($A162:$A192, MATCH(G162,$A162:$A192,1)),0,3):OFFSET(INDEX($A162:$A192, MATCH(G162,$A162:$A192,1)),7,3)).
This looks at the date in G162 and finds it in the list of days in A162:A192, moves over three columns, creates a range, and sums the range.


The Formula in G162 is =A162-DAY(A162)+8-WEEKDAY(A162-DAY(A162)). This returns 12/3/2023, the first Sunday of the month
The Formula in G163 is =G162+7. This returns 12/10/2023
The Formula in G164 is =G163+7. This returns 12/17/2023
The Formula in G165 is =G164+7. This returns 12/24/2023

The Formula in A162 is =EOMONTH(TODAY(),6)+1. This returns 12/1/2023
The formula in A163 is =IF(A162="","",IF(MONTH(A162+1)<>MONTH(A162),0, A162+1)). This returns 12/2/2023 and continues through the end of the month.

The only commonality it seems is the EOMonth function?!?

Questions, comments, ideas, suggestions?

Thanks in advance!

 

[kyddrivers]

xl2bb pieces to the above examples

Circular reference in A258:

CS +30 +60 day Lock Month template.xlsm
	A
258	3/1/2024
259	3/2/2024
Calls

Cell Formulas
Range	Formula
A258	A258	=EOMONTH(TODAY(),9)+1
A259	A259	=IF(A258="","",IF(MONTH(A258+1)<>MONTH(A258),0, A258+1))

Circular reference in AB19:

CS +30 +60 day Lock Month template.xlsm
	AB
19	December
SL AHT Occ Shrink

Cell Formulas
Range	Formula
AB19	AB19	=XLOOKUP(MAX(Calls!$H$162:$H$165), Calls!$H$162:$H$165, Calls!$G$162:$G$165)

Calls tab from the AB19 mention:

CS +30 +60 day Lock Month template.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
162	12/1/2023	2607	100%	2607	Fri		12/3/2023	14107
163	12/2/2023	1230	100%	1230	Sat		12/10/2023	14144
164	12/3/2023	0	100%	0	Sun		12/17/2023	14189
165	12/4/2023	2448	100%	2448	Mon		12/24/2023	6103
166	12/5/2023	2637	100%	2637	Tue
167	12/6/2023	2608	100%	2608	Wed		Sun	Mon	Tue	Wed	Thu	Fri	Sat
168	12/7/2023	2540	100%	2540	Thu		0	3	4	4	4	5	5
169	12/8/2023	2634	100%	2634	Fri
170	12/9/2023	1240	100%	1240	Sat
171	12/10/2023	0	100%	0	Sun
172	12/11/2023	2457	100%	2457	Mon
173	12/12/2023	2643	100%	2643	Tue
174	12/13/2023	2610	100%	2610	Wed
175	12/14/2023	2545	100%	2545	Thu
176	12/15/2023	2642	100%	2642	Fri
177	12/16/2023	1247	100%	1247	Sat
178	12/17/2023	0	100%	0	Sun
179	12/18/2023	2465	100%	2465	Mon
180	12/19/2023	2651	100%	2651	Tue
181	12/20/2023	2615	100%	2615	Wed
182	12/21/2023	2553	100%	2553	Thu
183	12/22/2023	2650	100%	2650	Fri
184	12/23/2023	1255	100%	1255	Sat
185	12/24/2023	0	50%	0	Sun
186	12/25/2023	0	0%	0	Mon
187	12/26/2023	2921	50%	1461	Tue
188	12/27/2023	2726	50%	1363	Wed
189	12/28/2023	2607	50%	1304	Thu
190	12/29/2023	2685	50%	1343	Fri
191	12/30/2023	1266	50%	633	Sat
192	12/31/2023	0	50%	0	Sun
Calls

Cell Formulas
Range	Formula
A162	A162	=EOMONTH(TODAY(),6)+1
B162:B192	B162	=IFERROR(XLOOKUP(A162, '\\p2pcredit.local\Fileshare\Prosper\Business Operations\IVR_Workforce\Forecast\01-Call Forecast Exports\Call Forecast Exports\[call forecast import to forecasts.xlsx]CS'!$G$2:$G$1000, '\\p2pcredit.local\Fileshare\Prosper\Business Operations\IVR_Workforce\Forecast\01-Call Forecast Exports\Call Forecast Exports\[call forecast import to forecasts.xlsx]CS'!$H$2:$H$1000), 0)
C162:C192	C162	=IFERROR(XLOOKUP($A162, 'Holiday factors'!$E$3:$E$29, 'Holiday factors'!$F$3:$F$29),1)
D162:D192	D162	=C162*B162
E162:E192	E162	=IF(A162<>"", WEEKDAY(A162), "")
A163:A192	A163	=IF(A162="","",IF(MONTH(A162+1)<>MONTH(A162),0, A162+1))
G162	G162	=A162-DAY(A162)+8-WEEKDAY(A162-DAY(A162))
H162	H162	=SUM(OFFSET(INDEX($A162:$A192, MATCH(G162,$A162:$A192,1)),0,3):OFFSET(INDEX($A162:$A192, MATCH(G162,$A162:$A192,1)),7,3))
G163:G165	G163	=G162+7
H163	H163	=SUM(OFFSET(INDEX($A162:$A192, MATCH(G163,$A162:$A192,1)),0,3):OFFSET(INDEX($A162:$A192, MATCH(G163,$A162:$A192,1)),7,3))
H164	H164	=SUM(OFFSET(INDEX($A162:$A192, MATCH(G164,$A162:$A192,1)),0,3):OFFSET(INDEX($A162:$A192, MATCH(G164,$A162:$A192,1)),7,3))
H165	H165	=SUM(OFFSET(INDEX($A162:$A192, MATCH(G165,$A162:$A192,1)),0,3):OFFSET(INDEX($A162:$A192, MATCH(G165,$A162:$A192,1)),7,3))
G168:M168	G168	=COUNTIFS($D$162:$D$192, ">0",$E$162:$E$192, G167)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H162:H165	Cell Value	top 1 values	text	NO

Let me know if you need any additional details.

Thanks again!