Validation based on the value in another cell

[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

 

[T. Valko]

What if C9 is neither "Level 1" nor "Level 2" ?

 

[supersub15]

Biff,

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

Carlos

 

[T. Valko]

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

 

[Billy Liddel]

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

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

 

[Billy Liddel]

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

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]

I can't get these to work. They both seem to fail

with Level 2 and A3 = 8000

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

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

 

[jossebalthasar]

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 !