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!?

Try Data Validation.
--
David Biddulph

"ashley" <(E-Mail Removed)> wrote in message
news:E40C4452-8DD5-4C3E-938E-(E-Mail Removed)...
>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!?

Data =>Validation

Allow: List

Source: S,B

"ashley" wrote:

> 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!?

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

That should do what you want.

HTH,
Paul



"ashley" <(E-Mail Removed)> wrote in message
news:E40C4452-8DD5-4C3E-938E-(E-Mail Removed)...
>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!?

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


"ashley" wrote:

> 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!?

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


--
Regards,

Peo Sjoblom



"ashley" <(E-Mail Removed)> wrote in message
news:E40C4452-8DD5-4C3E-938E-(E-Mail Removed)...
>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!?

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

"ashley" wrote:

> 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!?

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


"Mike H" wrote:

> 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
>
> "ashley" wrote:
>
> > 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!?

Nice


Peo


"Elkar" <(E-Mail Removed)> wrote in message
news:68779B2C-2668-497E-9890-(E-Mail Removed)...
> 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
>
>
> "Mike H" wrote:
>
>> 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
>>
>> "ashley" wrote:
>>
>> > 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!?