2 Part Formula

[Guest]

I need a formula that, once locked, the cells in Column A will only accept
uppercase B's or S's and nothing else (no other letters, numbers, characters,
etc.). HELP!?

 

[David Biddulph]

Try Data Validation.

 

[Guest]

Data =>Validation

Allow: List

Source: S,B

 

[PCLIVE]

You could use Data-Validation.
Allow:
List
Source:
B,S

That should do what you want.

HTH,
Paul

 

[Guest]

Data Validation > Allow: Custom > Formula:
=AND(OR(CODE(A1)=66,CODE(A1)=83),LEN(A1)=1)

 

[Peo Sjoblom]

You are using B's and S's does that mean you can allow for instance BS or
BBBB or SSSS etc?

 

[Guest]

Ashley,

For multiple Bs & Ss I had to resort to a macro:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
For x = 1 To Len(Target.Value)
If Mid(Target.Value, x, 1) = "B" Or Mid(Target.Value, x, 1) =
"S" Then
Else
MsgBox ("Illegal entry, Capital B or S only")
Target.Value = ""
End If
Next
End If
End Sub

Mike

 

[Guest]

Here's a Custom Data Validation Formula that can allow for multiple B's and
S's.

=LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"S",""))=0

HTH,
Elkar

 

[Peo Sjoblom]

Nice


Peo

Here's a Custom Data Validation Formula that can allow for multiple B's
and
S's.

=LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"S",""))=0

HTH,
Elkar