Restrict value input in a column based on another column

[AlexCHI]

Please help... How could I make it so the user can only input an "X" either in column "D" or column "E" (on the same row) but not in both columns? Basically if there is an "X" marked in one column (either "D" or "E" on the same row), the other column will be restricted to enter a value. Mich appreciated for your advice!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Input an "X" where appropriate.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D,E:E")) Is Nothing Then Exit Sub
    If Target.Column = 4 And Target = "X" Then
        If Target.Offset(0, 1) = "X" Then
            MsgBox ("You must enter a value in cell " & Target.Address(0, 0) & ".")
            Target.ClearContents
            Exit Sub
        End If
    ElseIf Target.Column = 5 And Target = "X" Then
        If Target.Offset(0, -1) = "X" Then
            MsgBox ("You must enter a value in cell " & Target.Address(0, 0) & ".")
            Target.ClearContents
            Exit Sub
        End If
    End If
End Sub

 

[FDibbins]

Another approach would be to use Data Validation, find it on the Data Tab/Data Tools/Custom

Apply something like this to each cell (or highlight he range), with each cell referencing the other 1...
=A2<>"x"

 

[Peter_SSs]

Also with Data Validation, rather than set the DV individually for each cell or each column, do them all at once:-

1. Select, say, D3:E20
2. Apply the Data Validation -> Allow: Custom -> Formula: =COUNTIF($D3:$E3,"X")<2 -> OK


Note that both the Data Validation method and the vba method suggested by mumps have drawbacks so you need to pick the one that best suits your needs.
- The DV will be defeated if the user pastes values from elsewhere into the range to be validated.
- The vba method is defeated if the user does not enable macros. Without further modification it would also be defeated if columns are inserted/deleted to the left of column D, or more than one cell in the target range is altered at once.

 

[AlexCHI]

Thank you Peter for the clear explanation comparing the drawbacks of the two methods. And you are right, I applied your Data Validation formula and it works if user just stick to entering value into the two columns that are to be validated (i.e., column D & column E) but is defeated when user pastes a value from elsewhere into column D & column E. Are there no solution to solve this with DV?

Also with Data Validation, rather than set the DV individually for each cell or each column, do them all at once:-

1. Select, say, D3:E20
2. Apply the Data Validation -> Allow: Custom -> Formula: =COUNTIF($D3:$E3,"X")<2 -> OK


Note that both the Data Validation method and the vba method suggested by mumps have drawbacks so you need to pick the one that best suits your needs.
- The DV will be defeated if the user pastes values from elsewhere into the range to be validated.
- The vba method is defeated if the user does not enable macros. Without further modification it would also be defeated if columns are inserted/deleted to the left of column D, or more than one cell in the target range is altered at once.

 

[Peter_SSs]

TAre there no solution to solve this with DV?

Not that I am aware of.