 # 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

# Re: Circular Logic problem

Could you please provide sample file with these 4 cells and variant of your formula?

# Re: Circular Logic problem

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)

# Re: Circular Logic problem

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.

# Re: Circular Logic problem

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

# Re: Circular Logic problem

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 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.

# Re: Circular Logic problem

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 @microsoft ought to look at this logic and not return a circular login error as it is truly only logic and not iterative calculations.