Forum Discussion
Circular Logic problem
I have 4 cells that perform a Vlookup and return a text string. Currently each cell looks at the other three cells for the presence of a text string "Chassis Left" and if that value appears in the other three then they look up in a second table that has Chassis Right. Because none of the other 3 will have "chassis left" value the first cell with "chassis left" persists and the other cells will have "chassis right"
The problem is I get a circular reference. I have tried to avoid it by uses the presence of chassis left in an offset function. I have tried a countif statement on the 4 cells and if a 1 is present then use the other vlookup table and nothing seems to get around the circular reference. I can have everyone enable iteration but the iteration count is one and it is a hassle.
Any thoughts on how to avoid a circular reference?
9 Replies
Could you please provide sample file with these 4 cells and variant of your formula?
Here is a spreadsheet with the just the circular reference cells included. You select from the yellow drop downlists and it switches from the left to the right table. I have tried indirect and other approaches but all give me the circular reference
That doesn't matter which formulas to use, in your logic result of calculations depends on the value which calculation return (i.e. circular reference). That's only to change the logic of calculations. In particular
Part of the formula in F14 is ,IF(AND($C$7="OGT-R300-G4",$T$2=0),
In T2 we have =COUNT(AD2:AD4)
In AD2 is =IF($F$22="internal Left",1," ")
Within F22 is ,IF(AND($C$7="OGT-R300-G4",$T$3=0),
In T3 is =COUNT(AE2:AE4)
In AE2 is =IF($F$14="internal Left",1," ")
Thus we returned back to F14. Quite often circular reference is when formula includes reference on itself. Your case is a bit more complex, you have chain of formulas which depends on each other, but actually first formula depends on it's own value returned by last formula in this chain.
- I can provide the file because it has company sensitive data in it but the formulas are
For each of the cells returning a description:
Cell F14
=IF(B14=" "," ",IF(AND($C$7="OGT-R300-G4",$T$2=0),VLOOKUP($B$14,Chassis_left,4,FALSE),IF($C$7="OGT-R300-G4",VLOOKUP($B$14,Chassis_Right,4,FALSE),IF($C$7="OGT-DE200-G4",VLOOKUP($B$14,Expandable_Desktop,4,FALSE),VLOOKUP($B$14,Compact_Reference,4,FALSE)))))
with cell T2
=IFERROR(IF(OR($F$22="Internal Left",$F$30="Internal Left",$F$38="Internal Left"),1,0),$P$7)
Cell F22
=IF(B22=" "," ",IF(AND($C$7="OGT-R300-G4",$T$3=0),VLOOKUP($B$22,Chassis_left,4,FALSE),IF($C$7="OGT-R300-G4",VLOOKUP($B$22,Chassis_Right,4,FALSE),IF($C$7="OGT-DE200-G4",VLOOKUP($B$22,Expandable_Desktop,4,FALSE),VLOOKUP($B$22,Compact_Reference,4,FALSE)))))
with cell T3
=IFERROR(IF(OR($F$14="Internal Left",$F$30="Internal Left",$F$38="Internal Left"),1,0),$P$7)
Cell F30
=IF(B30=" "," ",IF(AND($C$7="OGT-R300-G4",$T$4=0),VLOOKUP($B$30,Chassis_left,4,FALSE),IF($C$7="OGT-R300-G4",VLOOKUP($B$30,Chassis_Right,4,FALSE),IF($C$7="OGT-DE200-G4",VLOOKUP($B$30,Expandable_Desktop,4,FALSE),VLOOKUP($B$30,Compact_Reference,4,FALSE)))))
With Cell T4
=IFERROR(IF(OR($F$14="Internal Left",$F$22="Internal Left",$F$38="Internal Left"),1,0),P7)
CellF38
=IF(B38=" "," ",IF(AND($C$7="OGT-R300-G4",$T$5=0),VLOOKUP($B$38,Chassis_left,4,FALSE),IF($C$7="OGT-R300-G4",VLOOKUP($B$38,Chassis_left,4,FALSE),IF($C$7="OGT-DE200-G4",VLOOKUP($B$38,Expandable_Desktop,4,FALSE),VLOOKUP($B$38,Compact_Reference,4,FALSE)))))
With cell T5
=IFERROR(IF(OR($F$14="Internal Left",$F$22="Internal Left",$F$30="Internal Left"),1,0),P7)It seems to be this shouldn't generate a circular logic error as it gets resolved correctly with one interation and no math is done.
