Quick Formula question..
 

I'm not very good with formulas, but I'm trying to make one that will b
part of the data validation. I want it to make sure that the only thin
that the cell will accept is three alpha characters (and if not already
make them uppercase)

--
DejaV
-----------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...fo&userid=2262
View this thread: http://www.excelforum.com/showthread.php?threadid=38011

Quick Formula question..
 

Try this:

I'll do this for Cell A1:
DataValidation
Allow: Custom
Formula: =AND(ISTEXT(A1),LEN(A1)=3,EXACT(A1,UPPER(A1)))
Click [OK]

Note: That will allow numbers if:
1)they are preceded by an apostrophe (making them text)
or
2)they are with uppercase text

Consequently,
VALID
'123
ABC
A S<-note the space
D5L

NOT VALID
123<-numeric
aBc<-not all uppercase
d5L<-letters are not all uppercase
ABCD<-more than 3 chars

Will that work for you?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=380117

Quick Formula question..
 

On Fri, 17 Jun 2005 10:30:29 -0500, DejaVu
wrote:


I'm not very good with formulas, but I'm trying to make one that will be
part of the data validation. I want it to make sure that the only thing
that the cell will accept is three alpha characters (and if not already,
make them uppercase).

You need two steps to do this.

1. Data Validation to limit your entry to three alpha characters. What
exactly do you mean by an "alpha" character? If you mean something that can be
anything that is text, then:

=AND(ISTEXT(A1),LEN(A1)=3)

If you mean A-Z and a-z only, then:

=AND(LEN(A1)=3,OR(AND(A1="A",A1<="Z"),AND(A1="a" ,A1<="z")))

Since you suggest that you want lower case letters changed to upper case after
entry, you also need an event triggered macro. The code assumes entries in
column A, so you may need to edit the AOI.

To enter this, right click on the sheet tab; select View Code; and paste the
code below into the window that opens.

Enjoy:

==============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = [A:A]

Application.EnableEvents = False

If Not Intersect(Target, AOI) Is Nothing Then
For Each c In Target
c.Value = UCase(c.Value)
Next c
End If

Application.EnableEvents = True
End Sub

==========================


--ron

Quick Formula question..
 

See one response at your post in .misc

DejaVu wrote:

I'm not very good with formulas, but I'm trying to make one that will be
part of the data validation. I want it to make sure that the only thing
that the cell will accept is three alpha characters (and if not already,
make them uppercase).

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380117

--

Dave Peterson

Quick Formula question..
 

Sorry for the confusion...

I'm wanting them to only be able to enter 3 "Letters". _No_numbers_,
_no_spaces_, _no_special_characters_.

I want the end result to be all uppercase letters ("ABC"), not
lowercase, or mixed. If it is possible, I would like it to take
whatever they enter (Uppercase, Lowercase, or mixed) and change it to
uppercase (ex: Abc to ABC, or abc to ABC) as well as reject all entries
with spaces, numbers, or special characters.

Thanks to both of you for your help. Ron Rosenfeld, I tried your
private sub and it works great. My only thing is that it still allows
entries like 'A12' or 'a 1'. However, I think this will accomplish
what I am looking for.


The next thing I was wanting to do was put a drop down calendar on this
sheet. The odd thing that I want is to only show only Saturdays on the
calendar. For example, this month's calendar would have: June 4 2005,
June 11... June 18... and June 25.

I'm not sure if this is even possible.


Thanks for all your help already (to both Rons!!), and I really
appreciate your help with this next issue.


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380117

Quick Formula question..
 

Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!! Thanks
again for your help.

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380117

Quick Formula question..
 

On Fri, 17 Jun 2005 15:21:02 -0500, DejaVu
wrote:

Thanks to both of you for your help. Ron Rosenfeld, I tried your
private sub and it works great. My only thing is that it still allows
entries like 'A12' or 'a 1'. However, I think this will accomplish
what I am looking for.

Well, the SUB will change lower case to upper case.

However, an improved Data Validation formula will also flag an error on A12 or
'a 1'. Use this instead:

=OR(AND(A1="AAA",A1<="ZZZ"),AND(A1="aaa",A1<="zz z"))

That formula in combination with the SUB should do what you want.
--ron

Quick Formula question..
 

On Fri, 17 Jun 2005 15:43:06 -0500, DejaVu
wrote:


Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!! Thanks
again for your help.

DejaVu

You could either use Data Validation to disallow, for example, a leading
<space, assuming that is nothing that should be entered. Just put appropriate
error messages so your users know what they did wrong.

e.g. =LEFT(C1,1)< " "

or you could use another event macro to clear the cell if the user entered a
<space and nothing else.


--ron

Quick Formula question..
 

And you have another response at one of your other posts.

DejaVu wrote:

Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!! Thanks
again for your help.

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380117

--

Dave Peterson

Quick Formula question..
 

Ron Rosenfeld Wrote:


However, an improved Data Validation formula will also flag an error o
A12 or
'a 1'. Use this instead:

=OR(AND(A1="AAA",A1<="ZZZ"),AND(A1="aaa",A1<="zz z"))

That formula in combination with the SUB should do what you want.
--ron


Ron- I tried the this formula, but then it allowed all sorts of othe
entries (ex. aabbcc, a123). I may not be doing something right, s
just let me know if that is the case. Thanks again Ron

--
DejaV
-----------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...fo&userid=2262
View this thread: http://www.excelforum.com/showthread.php?threadid=38011

Quick Formula question..
 

On Mon, 20 Jun 2005 09:03:02 -0500, DejaVu
wrote:


Ron Rosenfeld Wrote:


However, an improved Data Validation formula will also flag an error on
A12 or
'a 1'. Use this instead:

=OR(AND(A1="AAA",A1<="ZZZ"),AND(A1="aaa",A1<="zz z"))

That formula in combination with the SUB should do what you want.
--ron


Ron- I tried the this formula, but then it allowed all sorts of other
entries (ex. aabbcc, a123). I may not be doing something right, so
just let me know if that is the case. Thanks again Ron.

I forgot to post to also check for LEN=3.

But you will still need the event macro (SUB).

It occurs to me that it would be simpler, since you need the event macro
anyway, to do the validation within the macro. So Clear out the data
validation, and just use this event macro.

Right click on the sheet tab; select View Code; and paste the code below into
the window that opens. Change the AOI reference to reflect the appropriate
range.

===============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim str As String

Set AOI = [A:A]

Application.EnableEvents = False

If Not Intersect(Target, AOI) Is Nothing Then
str = UCase(Target.Text)
If str Like "[A-Z][A-Z][A-Z]" Then
Target.Value = str
GoTo NormalExit
Else
GoTo ErrorExit
End If
Else
GoTo NormalExit
End If

ErrorExit:
MsgBox ("Only three letter entries allowed")
Application.Undo
Target.Clear

NormalExit: Application.EnableEvents = True
End Sub
========================


--ron

Quick Formula question..
 

Ron -
That makes more sense to me as well (putting everything into the code).
It makes it simpler for me to understand as well. Thanks for your help
on this. It worked perfectly. The only thing that I changed was:
Target.Clear
to
Target.ClearContents

This lets me keep the borders on the cell. Thanks again for for the
help!!

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380117

Quick Formula question..
 

On Mon, 20 Jun 2005 16:22:53 -0500, DejaVu
wrote:


Ron -
That makes more sense to me as well (putting everything into the code).
It makes it simpler for me to understand as well. Thanks for your help
on this. It worked perfectly. The only thing that I changed was:
Target.Clear
to
Target.ClearContents

This lets me keep the borders on the cell. Thanks again for for the
help!!

DejaVu

Thanks for the feedback. Glad we got it to work for you.


--ron