How to restrict entry of a field based on two other cells

I have a cell that needs to be less than two other cells. It also can't have any entry if one of those cells is blank. How would I enter this into data validation?

I'll give an example. Values in column E must be less than the corresponding values in column C and F. Also no value can be entered in column E if there is no value at all in column C.

specific examples:
C10 and has $10.00 and F10 has $5. So E10 must be $5 or less.
C10 is blank. So no value can be entered in E10 (as C10 is blank).


Thanks,

I'm not sure you can do this with built in Data Validation. I came up with a VBA procedure during the Worksheet_Change event that can at least notify the user of the requirements you have but it won't prevent them. In the module for your sheet enter the following code:

Please Login or Register  to view this content.

See the attached workbook and try it.

Originally Posted by TFiske

I was able to modify the code to delete invalid values without going into an infinite loop... See the code below and try the attached workbook.

Please Login or Register  to view this content.

Thanks for that. It was only an example, and the actual column I'm checking is I, and I'm checking it against G and L.

I noticed that in the check for empty columns, you checked for both columns C and F. I only need column G to have data for data entry in I to be acceptable.

I've changed the code to the below. Would that be correct?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngValidate As Range
Dim row As Long
Dim maxVal As Single
Dim chkVal As Single

With Me

Set rngValidate = .Range("I:I")

If Not Intersect(rngValidate, Me.Range(Target.Address)) Is Nothing Then

If IsEmpty(Target.Address) Then 'If the user deletes the value in column I then exits the sub

Exit Sub

End If

On Error GoTo Quit

row = Target.row 'Returns the active row of Column I`
chkVal = Target.Value 'Returns the value entered into column I`
maxVal = Application.WorksheetFunction.Min(.Cells(row, 7), .Cells(row, 12)) 'Returns the smaller of the two values in columns G and L

If chkVal = 0 Then

Exit Sub

End If

If IsEmpty(.Cells(row, 7)) Then 'Checks if Column G is empty, alerts user and deletes value entered into column I

MsgBox "Column G must have a value in Row#: " & row

Target.ClearContents

ElseIf chkVal > maxVal Then 'Checks if value entered into column I is less than the minimum value, alerts user and deletes value entered into column I

MsgBox "The value in Column I must be less than " & maxVal

Target.ClearContents

End If

End If

End With

Quit:

End Sub

That looks right, is it doing what you need it to do? I realised a minute ago that my code wont prevent the user from entering a value of 0, is that a problem?

Just a friendly FYI, theres a forum rule against quoting entire posts.

If your problem is resolved could you please mark this thread as SOLVED in the Thread Tools dropdown. Thanks.