excel 2003

[colmar]

I am trying to make a stock control work sheet and I am having problems with a circular reference.
Take cell B2 and place this formula in it =(A2+B2). I now have a circular reference.
I have turned on the iteration in Tools,Options calculation and limited the maximum iterations to 1.
My problem is once the formula has been dragged down to B12 and i start to fill in the worksheet
with data starting at A2 down to A12. all the data that has been calculated above is automatically altered. In fact you can place data any where on the sheet and the data in the circular reference cells will change. This also applies to excel 2010.
Please try this yourselves and hopefully you will come up with an answer. If not I will start to learn VBA in excel.

[AndreRet]

This part from MS -

Allow or correct a circular reference
When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference. Microsoft Excel cannot automatically calculate all open workbooks when one of them contains a circular reference. You can remove a circular reference, or you can have Excel calculate each cell involved in the circular reference once by using the results of the previous iteration. Unless you change the default settings for iteration, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first.

I am however not sure what you are trying to achieve here. If you want the values for A+B, show it in column C.

[colmar]

I am trying to achieve a running total in Cell B2. When data is put into A2 it is automatically added to the data in Cell B2 and the updated data is displayed in cell B2.

[AndreRet]

If I understand this correctly, A1 = 20, A2 = 15....
B1= 20 (A1), B2 = 35 (A1 + A2)....

Is that what you are trying to achieve?

[AndreRet]

If not, this is what I could get from MS. I have never used iterations before, so...

1.On the Tools menu, click Options, and then click the Calculation tab.
2.Select the Iteration check box.
3.To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
4.To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum change box. The smaller the number, the more accurate the result and the more time Excel needs to calculate a worksheet.

Why do you need to recalculate rather than showing the result in the next column?

[AndreRet]

You can also try THIS link with sample "code".

[colmar]

Not Quite. Cell B2 = A2 + B2 so if I later increase the value of the data in Cell A2 Cell B2 will increase accordingly by the value of the data put in Cell A2 + the value of the data already in Cell B2.
The problem I am having is. I can partly achieve this but if I drag the formula or write the same formula anywhere else on the sheet using different Cells ie Cell G4 = G4 + G3 it automatically alters the data in cell B2 by adding the data from cell A2 again. In fact if I input a number in any Cell on the sheet and hit the Enter or Arrow key the data in Cell B2 still changes.
This is now getting complicated it would be better to send you a screen dump

[AndreRet]

Yes please, I'm getting confused. Maybe if I see what you need, we can get a solution.:)

[monarchmk]

When you put Maximum iterations to 1 that means with every cell change there will be only one level of recalculation in worksheets. Your cell B2, with every cell change or manual recalculate with F9, will receive A2 value + B2 value. You will not need to change cell values, simple double click and move focus to another cell or F2 and Enter combination, on any cell, will initiate recalculation which will add another A2 value in B2 cell. That why with any change anywhere in sheet your value in B2 changed. Any change in worksheet, triggers Excel to recalculate whole worksheet and that main reason why value in B2 changes whole time. If you put eq. 100 iterations, B2 cell will get 100times A2 cell + B2 cell. This is NOT correct way of calculating cell. If you made circular reference in any other cell in worksheet you will get same "phenomenon". You will probably need VBA to achieve you original idea.
You can test your problem with this...
If you put cell like this
B2: =A2+B2, C2: =B2+C2, D2: =C2+D2 (all three with circular references) and you put iterations to 1... and put 1 on cell A2 with each recalculation (press F9) will get these values
A B C D
1 1 1 1
1 2 3 4
1 3 6 10
1 4 10 20
1 5 15 35
1 6 21 56

it will be funnier if you put iterations to 32767 (max value), when you press F9 cells values in B,C and D (in my example) will start to recalculate them self's...
so this is not a 'bug', it is a feature :).. use VBA
Here is VBA code for what you need
Right click on you sheet tab and choose View Code
now copy/paste this in new window

Private Sub Worksheet_Change(ByVal Target As Range)
    If  Target.Address = "$A$2" Then
        Cells(2, 2).Value = Cells(2, 2).Value + Cells(2, 1).Value
    End If
End Sub

Press Alt-F4(that closes Visual Basic Editor) and with every change of cell A2 in cell B2 you will have B2 value + A2 value without circular reference and you can safely return iterations to 100.