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.

colmar
0
Newbie Poster

## Recommended Answers

Jump to PostThis 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 …

Jump to PostWhen 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, …

## All 8 Replies

AndreRet
381
Senior Poster

colmar
0
Newbie Poster

AndreRet
381
Senior Poster

AndreRet
381
Senior Poster

AndreRet
381
Senior Poster

colmar
0
Newbie Poster

AndreRet
381
Senior Poster

monarchmk
24
Junior Poster in Training

AndreRet
commented:
Well executed.
+6

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.