Validation

D

Donna

I am trying to format a cell based on what a score type
entered in another cell. A teacher is going to enter
test scores, but if the score type is a "percentile", I
want him/her to only be able to enter whole numbers
between 0 an 99. If the score type is "Scaled", I want
him/her to only be able to enter whole numbers between
100 and 999. If the score type is "NCE", the number
should be in the format of ##.# and between 0.1 and 99.9.
Is there anyway to do a nested if statement or write VBA
to format a number based on the contents of another cell
and also give it a range? Or is there a way to create
lists and have a nested if statement refer to certain
lists based on the contents of the other cell?
 
J

Jason Morin

Select B1 and go to Data > Validation > Custom, and insert:

=IF(A1="percentile",OR(B1=ROW(1:100)-1),IF(A1="scaled",OR
(B1=ROW(100:999)),IF(A1="NCE",AND(B1>=0.1,B1<=99.9,LEN(MID
(B1,FIND(".",B1),255))=2),"")))

where A1 holds the score type and B1 holds the number to
enter.

HTH
Jason
Atlanta, GA
 
J

jeff

Donna,

Jason has a great idea. Here's a macro to
do about what you want. It, too, assumes you have
the score type in A1 and data is entered in C1 to C5.
(adjust as needed)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C5")) Is Nothing Then
Exit Sub
Application.EnableEvents = False
With Target
If Range("A1") = "Percentile" Then
If Target.Value < 0 Or Target.Value > 99 Then
MsgBox ("Bad teacher!! enter 0 to 99")
Target.Clear
End If
.NumberFormat = "0"
Else
If Range("A1") = "Scaled" Then
If Target.Value < 100 Or Target.Value > 999 Then
MsgBox ("Bad teacher!! enter 100 to 999")
Target.Clear
End If
.NumberFormat = "#00"
Else ' NCE
If Target.Value < 0.1 Or Target.Value > 99.9 Then
MsgBox ("Bad teacher!! enter 0.1 to 99.9")
Target.Clear
End If
.NumberFormat = "#0.0"
End If
End If
End With
Application.EnableEvents = True
End Sub

jeff
 
D

Donna

Thanks Jeff - great help
-----Original Message-----
Donna,

Jason has a great idea. Here's a macro to
do about what you want. It, too, assumes you have
the score type in A1 and data is entered in C1 to C5.
(adjust as needed)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C5")) Is Nothing Then
Exit Sub
Application.EnableEvents = False
With Target
If Range("A1") = "Percentile" Then
If Target.Value < 0 Or Target.Value > 99 Then
MsgBox ("Bad teacher!! enter 0 to 99")
Target.Clear
End If
.NumberFormat = "0"
Else
If Range("A1") = "Scaled" Then
If Target.Value < 100 Or Target.Value > 999 Then
MsgBox ("Bad teacher!! enter 100 to 999")
Target.Clear
End If
.NumberFormat = "#00"
Else ' NCE
If Target.Value < 0.1 Or Target.Value > 99.9 Then
MsgBox ("Bad teacher!! enter 0.1 to 99.9")
Target.Clear
End If
.NumberFormat = "#0.0"
End If
End If
End With
Application.EnableEvents = True
End Sub

jeff
.
Click to expand...
 
Top