Validation based on the value in another cell

  • Thread starter Thread starter supersub15
  • Start date Start date
S

supersub15

Hi all,

How can I base the validation in one cell on the content of another
cell?

Example:
If value is cell C9 = "Level 1"
I want the validation in cell A3 to force the user to enter a value
equal to or greater than "8000"

If value is cell C9 = "Level 2"
I want the validation in cell A3 to force the user to enter a value
equal to or greater than "4000"

Thanks.
Carlos
 
Biff,

I have a drop-down with validation in C9 where the user can only pick
Level 1 or Level 2.

Carlos
 
Try this...

Select cell A3
Goto Data>Validation
Allow: Custom
Formula:

=OR(AND(C9="Level 1",COUNT(A3),A3>=8000),AND(C9="Level
2",COUNT(A3),A3>=4000))

Uncheck: Ignore blank
OK out

--
Biff
Microsoft Excel MVP


Biff,

I have a drop-down with validation in C9 where the user can only pick
Level 1 or Level 2.

Carlos
 
I can't get these to work. They both seem to fail with Level 2 and A3 = 8000

The event macro is not very elegant but it is late here. If wrong values are
entered in A3 the minimum value is put there and a mesage box informs the
user of the allowed values. Just press enter to dismiss the message and enter
the correct value.

The code is copied into the Sheet code. Right-click the sheet tab, choose
view code and copy the macro into the sheet. return to the sheet and get
working.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("C9")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

If Target = "Level 1" Then

If Cells(3, 1) < 8000 Then
Cells(3, 1).Value = 8000
Cells(3, 1).Activate
MsgBox "Range A3 must be Greater or equal to 8000"
Exit Sub
End If

ElseIf Target = "Level 2" Then
Cells(3, 1).Activate
If Cells(3, 1) >= 4000 And _
Cells(3, 1) < 8000 Then
'do nothing
Else
Cells(3, 1).Value = 4000
Cells(3, 1).Activate
MsgBox "Range(A3) must be between 4000 and 7999"
Exit Sub
End If
End If

End Sub

Regards
Peter A

T. Valko said:
Try this...

Select cell A3
Goto Data>Validation
Allow: Custom
Formula:

=OR(AND(C9="Level 1",COUNT(A3),A3>=8000),AND(C9="Level
2",COUNT(A3),A3>=4000))

Uncheck: Ignore blank
OK out

--
Biff
Microsoft Excel MVP


Biff,

I have a drop-down with validation in C9 where the user can only pick
Level 1 or Level 2.

Carlos
Click to expand...
 
I think this validation formula works

=OR(AND($C$9="Level 2",$A$3>4000,$C$9="Level
2",NOT($A$3>7999)),AND($C$9="Level 1",$A$3>7999))

regards
Peter A

Billy Liddel said:
I can't get these to work. They both seem to fail with Level 2 and A3 = 8000

The event macro is not very elegant but it is late here. If wrong values are
entered in A3 the minimum value is put there and a mesage box informs the
user of the allowed values. Just press enter to dismiss the message and enter
the correct value.

The code is copied into the Sheet code. Right-click the sheet tab, choose
view code and copy the macro into the sheet. return to the sheet and get
working.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("C9")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

If Target = "Level 1" Then

If Cells(3, 1) < 8000 Then
Cells(3, 1).Value = 8000
Cells(3, 1).Activate
MsgBox "Range A3 must be Greater or equal to 8000"
Exit Sub
End If

ElseIf Target = "Level 2" Then
Cells(3, 1).Activate
If Cells(3, 1) >= 4000 And _
Cells(3, 1) < 8000 Then
'do nothing
Else
Cells(3, 1).Value = 4000
Cells(3, 1).Activate
MsgBox "Range(A3) must be between 4000 and 7999"
Exit Sub
End If
End If

End Sub

Regards
Peter A
Click to expand...
 
I can't get these to work. They both seem to fail
with Level 2 and A3 = 8000
Click to expand...

Hmmm...

It works for me. It was extensively tested.

Of course, it'll fail if you copy/cut paste or drag and drop into the cell.
But, that's just how validation works.


--
Biff
Microsoft Excel MVP


Billy Liddel said:
I can't get these to work. They both seem to fail with Level 2 and A3 =
8000

The event macro is not very elegant but it is late here. If wrong values
are
entered in A3 the minimum value is put there and a mesage box informs the
user of the allowed values. Just press enter to dismiss the message and
enter
the correct value.

The code is copied into the Sheet code. Right-click the sheet tab, choose
view code and copy the macro into the sheet. return to the sheet and get
working.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("C9")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

If Target = "Level 1" Then

If Cells(3, 1) < 8000 Then
Cells(3, 1).Value = 8000
Cells(3, 1).Activate
MsgBox "Range A3 must be Greater or equal to 8000"
Exit Sub
End If

ElseIf Target = "Level 2" Then
Cells(3, 1).Activate
If Cells(3, 1) >= 4000 And _
Cells(3, 1) < 8000 Then
'do nothing
Else
Cells(3, 1).Value = 4000
Cells(3, 1).Activate
MsgBox "Range(A3) must be between 4000 and 7999"
Exit Sub
End If
End If

End Sub

Regards
Peter A
Click to expand...
 
Dear all,
For excell validation, I want the validation to check the presence in another cell.
So, the user may only enter a number in cell A1 if there is a value in cell B1.

How do I do this ?
Thanks !
 
Back
Top