Forum Discussion
Circular Logic problem
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.
- Thanks Sergei -
Yes I understand the point as each cell needs to watch the other three to see if and instance of "internal Left" is entered. Once that occurs the switch is flipped in the other three cells and the lookup is redirected to the "right table". The only other alternative is to force the user to enter data from top to bottom and I don't want to enforce that requirement in the data entry.
So what I am hearing you say is I don't have a means other than allowing the circular logic and set the iterations at 1 which is plenty to choose the left and flip the switch to right. I really thing Pernille-Eskebo ought to look at this logic and not return a circular login error as it is truly only logic and not iterative calculations.
- 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.
