Using Circular References to our Advantage
May 30, 2003 - by Bill Jelen
I think I’m on the right track with a circular reference. Here’s my problem. I have two cells,
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
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
=A1+B1in 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.