Mar 03 2021 10:23 AM
Mar 03 2021 10:23 AM
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?
Mar 03 2021 11:18 AM
Mar 03 2021 02:05 PM
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.
Mar 05 2021 12:35 PM
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
Mar 05 2021 02:04 PM
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.
Mar 05 2021 03:05 PM
Mar 10 2021 09:52 AM
@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.
Mar 10 2021 10:01 AM - edited Mar 10 2021 10:05 AM
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!