If Cell1 has something Cell2 is blank?

H

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"
 
F

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
 
H

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.
 
H

HotRod

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

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
 
H

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
 
M

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
 
H

HotRod

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