Allow only one particular letter once in a group of cells

**bobrem** · 12-08-2010, 05:09 PM

I am trying to make this idiot proof. And I know what an idiot is. Me. I want to allow entry of only the letter x in 5 consecative cells. Once an x is placed in any of the 5 cells don't allow anything in the other 4 cells. Thanks in advance for your help.

**ChemistB** · 12-08-2010, 05:22 PM

Let's say your working area is A2:E20. Select your working area and go to Data >Data validation
Select Custom and type in this formula
=COUNTIF($A2:$E2,"x")<2 Then OK

If you want, modify your Error message to be more specific. Does that work for you?

**bobrem** · 12-09-2010, 12:07 PM

Thanks for the quick reply. Getting close I think. It works for "x" only. I want it to not allow any letter or number except "x" to be used. I am counting the number of "x's" so any other symbol isn't counted. Thanks.

**bobrem** · 12-09-2010, 12:52 PM

I want to use this in validation. I want =COUNTIF($A1:$E1,"x")<2) so that only one "x" in any of the five cells will be allowed combined with (A1:E1="x") which allows only the letter "x" to be used. Thanks.

**ChemistB** · 12-09-2010, 01:26 PM

Use AND

=AND(COUNTIF($A1:$E1,"x")<2), (A1:E1="x") ) How's that?

**bobrem** · 12-09-2010, 02:28 PM

I thought that should work but it gives me an error message. I selected cells A1 to E1, validation, custom, pasted the formula and hit OK.

**ChemistB** · 12-09-2010, 03:04 PM

Try this
=AND(COUNTIF($A1:$E1,"x")<2,COUNTIF($A1:$E1,"x")=COUNTA($A1:$E1))
The second part says the # of x's is the same as the # of cells containing values.