Question: I've got two columns in my Excel spreadsheet that I want to be mutually exclusive.
e.g.
B1 formula: IF(A1 contains "yes" then B1 set to "-") and also
A1 formula: IF(B1 contains "yes" then A1 set to "-")

Basically this should mean that when one cell is changed to "Yes", the other cell beside it automatically changes to"-"

When I try to do this in the excel formula builder it says that I've created a circular formula. Does anyone know what I can do to make this work?

Thanks

Answer: 
How about something like this:

```
' On cell A1
=IF(B1="Yes","-","Yes")

' On cell B1
=IF(A1="Yes","-","Yes")

```

The downside is that after A1 is given a value of "Yes" or "-", it no longer has the function as its value. This potentially isn't a issue - just leave B1 with the function and the set A1 as needed (B1 will update so you don't need to update it)

Not exactly your request, but hopefully it will help someone someday!
This can only be achieved in VBA. Copy the following code in the module of your worksheet (e.g. `Sheet1`):

```

Private mBlnIsRunning As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Me.Range("A1:B1")) _
        Is Nothing Then Exit Sub

    'Prevent endless loop
    If mBlnIsRunning Then Exit Sub

    mBlnIsRunning = True

    If Target.Address(False, False) = "A1" Then
        Me.Range("B1") = Negative(Target.Value)
    Else
        Me.Range("A1") = Negative(Target.Value)
    End If

    mBlnIsRunning = False
End Sub

Private Function Negative(varValue As Variant) As Variant
    'Change this function according to your needs
    If varValue = "yes" Then
        Negative = "-"
    ElseIf varValue = "-" Then
        Negative = "yes"
    Else
        Negative = "Please enter either yes or -!"
    End If
End Function

```