Circular reference

**hound** · 07-04-2009, 06:57 PM

Hi. I have a need to make two cells reference each other. ie the formula of A1 is "=B1" and the formula of B1 is "=A1". I want this so that my user can enter a number in either of these cells and it will then populate the other cell.

Excel errors when I open this files saying "Excel cannot calculate a formula. Cell references in the formula refer to the formula's result. etc etc"

I think I can resolve this by clicking the iterations box in the "Tools, Options, calculations" box but this sets the paramaters for the whole of excel not just this spread sheet. Any advise would be appreciated.

**deadlyduck** · 07-04-2009, 07:33 PM

If you enter this type of formula into these 2 cells, Excel won't be able to determine the relevant answer for either cell. It will see in A1 that the value depends upon what you entered in B1, but the value in B1 depends upon what you entered in A1. Hence, an infinite loop ("circular reference") arises.

You will need to force your users to manually enter a value in one cell only and then use a formula to populate the other cell

**hound** · 07-04-2009, 07:58 PM

thnaks...is there any way to assign a macro to an individual cell? That way I could allow the user to input in any cell and then use the macro to copy it to it's partner cell?

**shg** · 07-04-2009, 08:36 PM

You can have a macro to do that, but a) it eliminates the ability to Undo, and b) requires users to enable macros -- which is fine if you already require macros, but probably overkill for this requirement.

Can you explain what you're trying to do, and post an example?

**hound** · 07-04-2009, 09:03 PM

Thanks...Here is what I am trying to do. I have a grid where I need to record winning and losing scores between two people. So the winning score for person A is the losing score for person B. I want the user to be able to enter in either person A or Person B. Please see attachement.