Dealing with Circular References in Excel when there is no apparent circular reference

Question

Forgive me if this isn't the right place, but I have seen a couple of posts on here relating to excel troubles like this and they don't really help. I am creating a tournament handicap tracker for my snooker league, which involves a main sheet with every week's handicap, as well as individual week sheets for tracking the scores between players every week.

Here is the basic layout of the overview page. (I've removed names for privacy purposes)

Then, each week has its own page that tracks scores and calculates handicaps.

The trouble is the references between each week and the handicaps stored on the main overview page. For example, the "previous handicap" for week 1 is hard coded, but for every other week, it depends on the value in the previous week on the main page. I.e, the "Previous Handicap" for Week 2 is a copy of the "Week 1" column on the main page.

To do this, I have used the following formula =IFERROR(VLOOKUP([@Player],TblPlayerOverview,MID(CELL("filename"), FIND("]",CELL("filename"))+6,500)+2), "N/A"), where TblPlayerOverview is the main table.

This initial calculation works great, but as soon as I try and calculate it for Week 3, I get a circular reference error, and the week 2 previous handicap column seems to iterate everytime I try and calculate week 3. This leads me to believe there is somehow a circular dependence between week 2 and the main page, but I honestly cannot find it and I have looked for quite some time. I would appreciate some help.

The calculation for copying over the weeks new handicaps to the main page goes as follows: =VLOOKUP([@Player],INDIRECT("'"&INDEX(TblPlayerOverview[#Headers], COLUMN())&"'"&"!A17:D32"),4).

I currently only have three week sheets as I deleted the newer ones to try and simplify the problem.

I understand that this may be difficult to comprehend just from the screenshots, so I have included a link to my google drive in which the excel file has been uploaded.

I will appreciate any and all help as I am truly lost.

Thanks, Ben

SnookerLeagueResults.xlsx

Answer 1

Edited/Updated Answer following comments

After discussing with OP in the comments to this answer and using a simplified file, the issue is because there is no cell reference value when CELL("filename") is used. Reading the documentation for the formula, there is an optional argument to specify which cell you want information about and that "if omitted, the information specified... is returned for the cell selected at the time of calculation."

This argument was not included in OP's formula so it would evaluate the filename based on the current cell that is selected. This means that when the previous handicap formula on the Week 3 sheet was selected and calculated, anywhere that CELL("filename") was used would now refer to the active cell on the Week 3 sheet. But as CELL("filename") is also on the Week 2 sheet, this formula would be referring to the Week 3 sheet and so be a circular reference.

This means the solution is to specify the cell reference argument whenever CELL("filename") is used so that the cell it refers to is constant and doesn't change when the selected cell changes. What is used in the argument doesn't really matter, using cell A1 of each sheet is probably easiest. So the new formula for the original file should be:

=IFERROR(VLOOKUP([@Player],TblPlayerOverview,MID(CELL("filename",A1), FIND("]",CELL("filename",A1))+6,500)+2,FALSE), "N/A")

Sidenote, I've also added a 'FALSE' argument to the end of the VLOOKUP formula to ensure it retrieves an exact match, not an approximate one.

Original Answer

The formula in B18:B32 on the 'Week 3' sheet refers to the 'TblPlayerOverview' table on the 'Player Overview' sheet. But column F in this table refers to the 'A17:D32' range on the 'Week 3' sheet (and so is implicitly looking at the B18:B32 range). This is your circular reference.

I think the way to overcome this would be to change the table reference to the previous week/sheet, so Week 3 refers to the previous handicap in the Week 2 table rather than the 'Player Overview' sheet.