Circular Logic problem

Occasional Contributor

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

@Dave_Obert 

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

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.   

@Sergei Baklan 

 

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

@Dave_Obert 

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

@Dave_Obert Here is a suggestion that can help you:

Since you use a drop down make the drop down menu change based on whether any of the other cells have already been populated.  For example you could slightly change the item names in the 2 lists (left vs right) so you can identify each.  In the attached I did this dynamically but you could same some formula complexity if you made the changes to the list directly.

So now the data validation is looking at this new list which is appended with a "-L" if nothing is entered yet or a "-R" if there is a value already.  Then the formulas check for that last letter and do the look up accordingly.  This technique is pretty flexible but 1 caveat I did find is that if you delete 3 items and then change the 1 item remaining or after entering the 1st item you change it to something else, it will be a "-R" because as far as the dropdown is concerned you already have an item filled in.

Thank you for the idea. That might work.  The decriptors people have to choose from are identical but I could modify them and then as you suggest use the drop down to account for it.   My worry is it will double the list of choices for the customer and seem overwhelming.  But it is certainly an option.   The best I have seen so far!

The whole point is that the user will only get 1 set of options and it is transparent to them