Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

2 Part Formula

0 views
Skip to first unread message

ashley

unread,
Jul 10, 2007, 2:32:00 PM7/10/07
to
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

unread,
Jul 10, 2007, 2:38:12 PM7/10/07
to
Try Data Validation.
--
David Biddulph

"ashley" <ash...@discussions.microsoft.com> wrote in message
news:E40C4452-8DD5-4C3E...@microsoft.com...

Toppers

unread,
Jul 10, 2007, 2:40:01 PM7/10/07
to
Data =>Validation

Allow: List

Source: S,B

PCLIVE

unread,
Jul 10, 2007, 2:43:40 PM7/10/07
to
You could use Data-Validation.
Allow:
List
Source:
B,S

That should do what you want.

HTH,
Paul

"ashley" <ash...@discussions.microsoft.com> wrote in message
news:E40C4452-8DD5-4C3E...@microsoft.com...

Teethless mama

unread,
Jul 10, 2007, 2:54:02 PM7/10/07
to
Data Validation > Allow: Custom > Formula:
=AND(OR(CODE(A1)=66,CODE(A1)=83),LEN(A1)=1)

Peo Sjoblom

unread,
Jul 10, 2007, 2:54:59 PM7/10/07
to
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" <ash...@discussions.microsoft.com> wrote in message
news:E40C4452-8DD5-4C3E...@microsoft.com...

Mike H

unread,
Jul 10, 2007, 3:22:01 PM7/10/07
to
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

Elkar

unread,
Jul 10, 2007, 3:58:05 PM7/10/07
to
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

unread,
Jul 10, 2007, 4:27:42 PM7/10/07
to
Nice


Peo


"Elkar" <El...@discussions.microsoft.com> wrote in message
news:68779B2C-2668-497E...@microsoft.com...

0 new messages