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"

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

--
Regards
Frank Kabel
Frankfurt, Germany

"HotRod" schrieb im Newsbeitrag
...
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"

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" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

"HotRod" schrieb im Newsbeitrag
...
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"

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

--
Regards
Frank Kabel
Frankfurt, Germany

"HotRod" schrieb im Newsbeitrag
...
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" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

"HotRod" schrieb im Newsbeitrag
...
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"

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

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in
message ...
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 ' for
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 already
an x before

- Mangesh


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


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


- Mangesh


---
Message posted from http://www.ExcelForum.com/