Excel formula to set cell value

1
votes

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

2
excelsetexcel-formula
So, A1 and B1 are options for a single 'field', I take it? Are their values used elsewhere? – Saladin Akara
Also, which version of Excel are you using? – Saladin Akara
Yes A1 and B1 are example cells. What I want to happen is that when the value "Yes" is placed in A1, the value of B1 changes to "-" but also when "yes" is placed into B1, the value of A1 changes to "-" Using Excel 2011 for Mac – Adam
Unfortunately this is NOT achievable via formulas. Only VBA may help. – Peter L.

2 Answers

3
votes

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!

1
votes

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