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.
Jump to Post
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 …
Jump to Post
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, …
All 8 Replies
We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.