How do I make two cells dependent on one another without circular error?

Question

In Excel 2013, I have cells A1, A2, and A3 along with B1, B2, and B3. Column A is a yearly calculation and Column B is a weekly calculation.

For example, if the user updates A1 (e.g., 5200), I want to auto-update B1 (e.g., 100). If the user updates B1 (e.g., 50), I want to auto-update A1 (e.g., 2600). Likewise for A2-B2 and A3-B3.

When I try to monitor both, it creates a circular issue and Excel crashes.

Answer 1

Don't use formula at all then. Use a _Change event like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub

Application.EnableEvents = False  'Prevent infinite looping

If Target.Column = 1 Then
    'User has changed something in column A:
    Target.Offset(0, 1).Value = Target / 52
Else
    'User has changed something in column B:
    Target.Offset(0, -1).Value = Target * 52
End If

Application.EnableEvents = True
End Sub