data validation question (again)

[spiritwater2004]

hopefully someone out there can help me once again....
I would like to limit the entries into an excel field to :
1. "A"
2. "P"
3. "<" & any number formatted ##.##
4. ">" & any number formatted ##.##
5. any number
example: B1 can be "A"
B2 can be "P"
B3 can be "< 25.36"
B4 can be "> 12.00"
B5 can be "20.00"

how can I do this?

 

[DaleWatson123321]

Here you go... formulas for Data Validation:

1) =CODE(B1)=65

2) =CODE(B2)=80

3) =AND(LEN(B3)=7,CODE(LEFT(B3))=60,ISTEXT(B3),VALUE(MID(B3,2,999))<25.36)

4) =AND(LEN(B4)=7,CODE(LEFT(B4))=62,ISTEXT(B4),VALUE(MID(B4,2,999))>12)

5) =ISNUMBER(B5)

Note: for #5, you specified "any number", but the example you gave shows 2 decimal places. This suggests all you need to do (in addition to ensuring the entry is a NUMBER with the above formula), is to format the cell to 2 decimal places.

If by your example of "20.00", you meant any number up to 100, you could use the following formula...

=AND(ISNUMBER(B5),B5>0,B5<100)

(or instead of the "Custom" option, you could use "Whole Number", and specify the MIN and MAX numbers)

Hope this helps.

Regards, Dale Watson

 

[spiritwater2004]

It appears that you are giving me the validation formulas for several fields which I wish was my problem. I need the validation to apply to one column. In other words, for every field in column B the user can only enter an "A", or "P", or a "greater than" or "less than" sign and a number. I have tried:

If target.Value = "A" Then Exit Sub
If target.Value = "P" Then Exit Sub

goodNum = Application.WorksheetFunction.IsNumber(Right(target, 4))
if goodNum=true then exit sub

I've also tried:

If Left(target,1)="<" then ...

as well as combination of other things but I'm obviously trying the wrong approach. I'm very familiar with access and vb, but Excel doesn't seem to like the same syntax.
Can the "=CODE(target)=65" be used?

 

[spiritwater2004]

I tried...........

If code(target) = 65 Then Exit Sub
If code(target) = 80 Then Exit Sub
If code(Left(target,1)= 92 AND(ISNUMBER(right(target,4)))


.........which also gives me an error. The CODE function gives me an error saying "sub or function not defined", but "visual basic for applications" and "Excel 10.0 object library" is referenced. Can anyone help?

 

[spiritwater2004]

Please cancel request. The following worked:
If Application.Intersect(target, Range("J3:J65536")) Is Nothing Then Exit Sub
If target.Value <> "A" Then

If target.Value <> "P" Then
If Left(target, 1) <> "<" Then
If Left(target, 1) <> ">" Then

goodNum = Application.WorksheetFunction.IsNumber(target)

If goodNum = False Then
MsgBox "Data not in allowed entries of :'A', 'P', or '< ##.##','> ##.##'"

Thanks anyway.

 

[PHV]

You may even replace this:
goodNum = Application.WorksheetFunction.IsNumber(target)
By this:
goodNum = IsNumeric(target.Value)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244