Using Circular References to our Advantage

May 30, 2003 - by Bill Jelen

Michelle asks,

I think I’m on the right track with a circular reference. Here’s my problem. I have two cells, A1=5 and B1=5. What I want to do is have B1 memorize 5, and then I want to add a new value to A1, say A1 now equals 10. Can I have a B1 formula that memorizes 5, but also adds 10? So now B1=15?

Usually, circular references are a bad thing, but sometimes they can be used to our advantage. Here is the non-macro way of doing what you want to do. It will only work in certain situations.

From the Excel menu, select Tools > Options.
Go to the Calculation tab. Check the box for Iterations. Change Maximum Iterations to 1.
Click OK to close the options dialog box.
Enter 5 in Cell A1.
Enter 0 in Cell B1
Enter =A1+B1 in Cell B1
Now, as you enter new values in A1, the entry in B1 will remember the old total and add the value from A1.

Here is the HUGE limitation. You can not enter any values elsewhere on the sheet! Any time that you enter a value or the sheet is recalculated, the value in A1 will be added to the value in B1. Thus, by hitting F9 a bunch of times, you will watch B1 increase by 5 for each F9.

The safer way to do this is with a little event handler macro. You will need to add this code to the code pane for Sheet1 (assuming that you are working on Sheet1). The event handler code would be as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
	If Target.Address = “$A$1” Then
		Application.EnableEvents = False
		Range(“B1”).Value = Range(“B1”).Value + Target.Value
    Application.EnableEvents = True
	End If
End Sub

This bit of code is run any time that a cell is changed on the sheet. Target is a special object variable that tells which cell was changed. The event handler checks to see which cell was just changed. If the cell was A1, then it will add the value in A1 to B1. We have to turn off event handlers while changing B1 so that the event handler doesn’t call itself again.