Avoiding Circular Reference

[ffffloyd]

Here's a tricky one.

Let's say I have a sheet called Category. Column A contains code; column B contains description.

Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given.

I thought I might be able to do something like:

in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"")
in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"")

(and then copy the formulae down the rest of the columns of course)

In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.

However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work.

This sort of situation must crop up from time to time. How do we get around it?

[kellyfspringer]

I just tested this, and it seems to work:
Go to Tools>Options>Calculation

Click the "Irritations" Checkbox. It seems that this stops excel from re-calculating and finding the circular reference. When I did it, I got an N/A, so you would need to add an "IF(ISNA())" to your formula. 'Hope this helps a little.

[ffffloyd]

"Irritations"

Yes, I clicked the "Iterations" checkbox and, in fact, gave it a maximum of 1 thinking it might make it run quicker and, lo and behold, it works!

Thanks Kelly.

[kellyfspringer]

Ha! Freudian slip, maybe I was having a bad day. Well, glad it got you in the right direction!!