Help with a possible circular reference

**Scubabuddy** · 02-03-2017, 08:23 PM

I have a general question about excel that is a little complicated to explain so I'm going to try my best. This is something I've been doing for years and have always thought that there has to be a better way. What I am trying to do is manipulate the value of cell a IN CELL A. Of course if I do this I get a circular error. I'm trying to figure out a work around and I can't imagine that I am the only person who thought the correct way of doing this is far too inefficient.

cell a cell b
VALUE manipulation of cell a

After manipulating the value of cell a in cell b, I have to copy and paste the value of cell b in cell b to write over the formula
Then I do a cell a = cell b formula
then I copy and paste the new value of cell a in cell a
then I can safely delete cell b

Is there a better way other than writing a macro?

**gmr4evr1** · 02-03-2017, 08:46 PM

So, you want cell A to reference cell B?
Do you want cell A to always have the value of cell B in it, or just maintain the last value that was in B?
You say you write over the formula in cell B...what is the formula?

Maybe show or post an example?

**FDibbins** · 02-04-2017, 01:59 AM

There is no formula way to get around a circ ref error, you simply cannot have 2 cells dependent on each other, and not get a circ ref error. At best, you can set iterations (the number of times excel goes through that loop), but you will still have the error

**MrShorty** · 02-04-2017, 11:16 AM

I agree with gmr4vr -- a real example of what you are trying to do would be most helpful.

I frequently use circular references/iteration to solve problems. When I do, I am using well defined algorithms such as the Newton-Raphson method for finding roots (http://www.excelforum.com/showthread.php?t=1111494 ) or a successive approximations algorithm. Circular reference/iteration works well for these kind of algorithms (when they converge quickly).

Your description of your problem sounds like it only requires a single iteration, which will be hard to control in Excel. I know that people despise helper cells, but, if 1 iteration is really all that is needed, I would think it would be easier to add a 3rd cell to the mix. You already have ainitial, and a formula for b based on ainitial. I would probably add a 3rd cell (remember that you have millions of cells at your disposal) to calculate afinal based on b, and not bother writing that value back into ainitial.