Circular Reference Troubleshooting

[thesonofdarwin]

I'm usually pretty good at avoiding circular references, or troubleshooting them if I run into one. But this one has me a bit perplexed. I'm getting a circular reference for simply mirroring a cell and the cell that's mirroring isn't included in any of the calculations that I can discern.

In the attached workbook, if I get rid of Temp!L1 everything works correctly. Temp!L1 isn't used in any of my formulas because its value is entirely dependent upon the ranking results on the Main Page, so I'm unsure how simply referencing this gives me an error. Any clues from you guys?

[Richard Buttrey]

Hi,

There seem several circ refs. One of them is AI1 on Temp which refers to C3 Main page, and C3 Main Page, by virtue of its use of M2:M11 indirectly references AI1 since the Match in M3 evaluates to an Offset Value of 35, and 35 columns offset from column A is AI.

[thesonofdarwin]

Hi,

There seem several circ refs. One of them is AI1 on Temp which refers to C3 Main page, and C3 Main Page, by virtue of its use of M2:M11 indirectly references AI1 since the Match in M3 evaluates to an Offset Value of 35, and 35 columns offset from column A is AI.

C3 on Main Page shouldn't reference AI1. The match in M3 evaluates to AJ2. For some reason, although it's evaluating as Temp!AJ2 as the cell reference, it's saying it's zero when it's not. I thought maybe it was the merged cells, but unmerging did nothing to resolve the issue.

AI1 (Temp) = C3 (Main)
C3 (Main) -> AJ2 (Temp, indirectly)
AJ2 (Temp) -> Y3 (Temp) -- This should be the end of the reference.

M3 (Main) definite seems to be the issue. I'm just not sure why because it appears to evaluate correctly. If I simply change the formula in M3 to a direct reference to AJ2 (Temp) everything works fine. However, I need M3 to be dependent upon B3, hence the original reference. L1 (Temp) could also be an issue, but I'm not entirely sure how to work around that.

[vlady]

B3=IF(Temp!$AB$1="","",Temp!$AB$1)


Column M=IF(B3="","",OFFSET(Temp!$A$1,1,(MATCH(B3,Temp!$A$1:$AS$1,0)-1+8),,))

the match is looking at the start A1 to AS1 which intern is the position of the values in B3 -->so eventually it will past thru Cell AB1 making a cell ref error.

you could try to change the cell ref of the offset just to make comparison

-testing purposes only.
Column M=IF(B3="","",OFFSET(Temp!$A$1,1,(MATCH(B3,Temp!$AC$1:$AS$1,0)-1+8),,)) ->from A1 to AC1 just to bypass AB1

[thesonofdarwin]

If that were the case, shouldn't deleting the references in column B and manually entering them resolve the issue? I tried that and it did not.

It seems the problem is heavily centered around the ranking. If I delete column C everything resolves. The ranking will be very key feature in this spreadsheet, so that's not the easy solution I want :D

One way I found around it which I really don't want to do, is to number sequentially in Main!A. Then the references in Temp that used to reference the rank values would instead reference the sequential numbers. Then force an autosort everytime something is changed so the ranks match up with the sequential numbers. Too many chances for something to go wrong using that method, such as when ranks return the same value if they are equal.

[vlady]

ahh, i see it now.

how about

=IFERROR(RANK(M2,$M$2:$M$11,0),"")

edit: nope that's a no.

[thesonofdarwin]

No go, unfortunately.

Using that method I still get the circular reference. The strange thing though is if I delete the rank reference in C2 and instead move it to C4 it will work. As long as I don't delete B2. If I delete B2 I get the circular reference again... http://i.imgur.com/o71J1sp.jpg

I'm not sure I've ever broke something so bad that was supposed to be easy!

[vlady]

OK.. error in Cell L(merged l1,l2,l3) in Temp tab.

unmerge the cells then transfer the formula to L2 , to make it look like it is still merge.

[thesonofdarwin]

vlady is a genius. Still a bit hard to understand why it was causing a problem, but that definitely fixed it.

[vlady]

Thanks for the kind words. Help is always present in the forum.

Regards,
Vladimir