Forum Discussion
Dave_Obert
Mar 03, 2021Copper Contributor
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 ...
Dave_Obert
Mar 03, 2021Copper Contributor
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)
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)
Dave_Obert
Mar 03, 2021Copper Contributor
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.