Forum Discussion
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
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.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!
