Can't See or Understand Circular Reference

[Skiier89]

Hello,

I am building a workbook. The basic structure is:

1) First sheet is a data set. In column A are names. In the columns B, C, D and so on are dates.
2) Subsequent sheets in the workbook are dedicated sheets for each year (i.e. 2018, 2019, 2020, 2021, etc)
3) Sheets are copied each year to the newest year. There are local named ranges saved on each sheet that are referenced in lookup functions throughout the workbook.
4) I keep getting a circular reference error and have no clue why. Is anyone able to take a look at the Excel data below and lead me in the direction why there is a circular reference?

Disclaimer - I understand circular references and can solve / remedy them on an intermediate level. But this issue I cannot understand. I think it has something to do with the local named ranges, which I would really like to keep. Other users will be working in this workbook and I cannot assume that other users will know how to correctly update named ranges each year so the lookup functions throughout the workbook preform accurately (i.e. index match referring to the correct year's data on each individual worksheet).

Capital Reconciliation (Mr. Excel Ref).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
7	Z ID	37 Partners	0281	Beginning %	Beginning Capital	Transferor	Transferee	Transferred Capital	Effective %	Contribution	CY Net Income (Loss)	Income Percentage	Cash Distribution	Ending %	Ending Capital
8	0281	A		-	-	Transferor				$ -	0	#DIV/0!	0
9	Z913	C		0.40	-							#DIV/0!	0	0.40	#VALUE!
10	Z405	D		-	-	Transferor						#DIV/0!	0		0
2016

Cell Formulas
Range	Formula
C7	C7	=IF(ISBLANK($B$9),"",COUNTA('2016'!ZID))&" Partners"
D7	D7	=IFERROR($B$8,"SHIFT TOTAL ROW")
B8:C44	B8	=IFERROR(FILTER(AllPartners,MMULT((Dates>=$A$3-365)*(Dates<=$A$3)*(Data>0),SEQUENCE(COLUMNS(Dates),,,0))),"-")
E8:E10	E8	=IFERROR(INDEX(INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!AllData"),MATCH($B8,INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!ZID"),FALSE),MATCH("Ending %",INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!Headers"),FALSE)),0)
F8:F10	F8	=IFERROR(INDEX(INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!AllData"),MATCH($B8,INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!ZID"),FALSE),MATCH("Ending Capital",INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!Headers"),FALSE)),0)
G8:G10	G8	=IF(OR(INDEX(PartnershipTable,MATCH($B8,PartnershipTable_ID,FALSE),MATCH(G$6,PartnershipTable_AllHeaders,FALSE))=E8,H8="Transferee"),"","Transferor")
H8:H10	H8	=IF(ISNA(VLOOKUP($B8,INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!ZID"),1,FALSE)),"Transferee","")
M8:M10	M8	=L8/$L$5
P9	P9	=INDEX(INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!AllData"),MATCH($B9,INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!ZID"),FALSE),MATCH("Ending Capital",INDIRECT("'"&RIGHT(TEXT($A$3-365,"yyy"),4)&"'!Headers"),FALSE))+I9+K9+L9-N9
P10	P10	=F10+I10+K10+L10-N10
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
AllPartners	='Partnership Table'!$A$6:$B$63	G8:G10, B8
Data	='Partnership Table'!$C$6:$AF$63	G8:G10, B8
Dates	='Partnership Table'!$C$5:$AF$5	B8, G8:G10
'2016'!Partners	=OFFSET('2016'!$B$7,1,0,COUNTIF('2016'!$B$8:$B$50,"Z*"),1)	E8:H8, C7:D7
PartnershipTable	='Partnership Table'!$A$6:$AD$63	G8:G10, B8
PartnershipTable_AllHeaders	='Partnership Table'!$A$5:$AD$5	G8:G10
PartnershipTable_ID	='Partnership Table'!$A$6:$A$63	G8:G10, B8
PartnershipTable_Name	='Partnership Table'!$B$6:$B$63	G8:G10, B8
'2016'!ZID	='2016'!$B$8:$B$48	E8:H8, C7:D7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8	Expression	=H8="Transferee"	text	NO
B8	Expression	=G8="Transferor"	text	NO
B9:B11	Cell	contains an error	text	NO
D7	Cell Value	="Shift total row"	text	NO

 

[Skiier89]

Please ignore - I figured out the issue but don't know how to delete thread.

 

[Alex Blakenburg]

You can post what you did to find it and to fix it and mark that as a solution.

For future reference there is a link below to a macro that finds circular references.
Find circular references within your workbook

 

[Skiier89]

I was not accounting for leap years (2016 and 2020 are leap years).

Defined ranges on each sheet are local ranges, so as sheets are copied the name of the ranges are updated as well. For instance, when sheet 2015 is copied to 2016, the local range 2015!Name is updated in the name manager to 2016!Name.

To call upon local defined ranges in formulas on other sheets, I use and indirect formula and a date input in another cell to return the name of the defined range. If cell A1 had 12/31/2016, then to call upon the 2015 locally defined range, I use for instance indirect("'"&(A1-365)&"'![named range]!) to return the '2016'![name range].

However, during leap years there are more than 365 days between the current year end 12/31/2016 and the prior year end 12/31/2015.

Therefore even though the local range 2016!Name exists on sheet 2016, the argument "-365" in the indirect function does not return "2015" but "2016". A circular reference emerges on the 2016 sheet because formulas for the current year (2016) use cells that are derived from lookup functions referring to their own value.

Hope this makes sense... it's was quite a lot to think through but so simple once I figured it out from evaluating the formula..