If Cell1 has something Cell2 is blank?

[HotRod]

I have 6 Cells S,T,U,V,W & X and I only want to allow the user to put an "X"
in one of them in that row. How can I write a "IF" so that only one of the
6 cells contains a "X"

 

[Frank Kabel]

Hi
if you have the cells A1:F1 try the following:
- select these cells
- goto 'Data - Validation - Custom'
- enter the following formula
=COUNTIF($A1:$E1,"X")<=1

 

[HotRod]

This generates an error if someone tries to enter text into another one of
the boxes but it doesn't really solve my problem. I'd prefer not to
interrupt the user.

 

[Frank Kabel]

Hi
but what should happen if the user tries to enter two 'X' values?

 

[HotRod]

Then the new "X" should stay and the other "X" should be removed. Like
"Radio Buttons" in VB.

 

[mangesh_yadav]

in that case you need a macro something like this

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row = 1) Then ' for row 1
If (Target.Column >= 1 And Target.Column <= 6) Then ' fo
columns 1 to 6
If (Target.Value = "x") Then
For i = 1 To 6
If (i <> Target.Column And ActiveSheet.Cells(1, i
= "x") Then
ActiveSheet.Cells(1, i) = ""
End If
Next i
End If
End If
End If
End Sub

note that I put a blank incase a new x is entered and there is alread
an x before

- Manges

 

[HotRod]

How do I apply this to each row in the sheet, so that each row operates as
it's own group? Please don't tell me I need to re-write the code for each
row.

THANKS for the help

 

[mangesh_yadav]

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column >= 1 And Target.Column <= 6) Then ' fo
columns 1 to 6
If (Target.Value = "x") Then
For i = 1 To 6
If (i <> Target.Column An
ActiveSheet.Cells(Target.Row, i) = "x") Then
ActiveSheet.Cells(Target.Row, i) = ""
End If
Next i
End If
End If
End Sub


- Manges

 

[HotRod]

This worked great THANKS, not only that but I know have a really good idea
of what other stuff I can incorporate. THANKS