Excel Validation Help Please

[TheKutts]

Hi - I have an excel sheet that has 3 fields:-

C2 - Trip Limit
C3 - Trip Start Date - dd/mm/yyyy
C4 - Trip End Date - dd/mm/yyyy

If a user enters a Trip Limit of 20 in C2 and if the End Date in C3 minus the Start Date in C4 is more than 20, I would like an error message to either warn the user that the Trip Limit needs to be checked or prevent the user from proceeding until they have changed the Start and End Date.

However, if the user enters N/A or NA in C2 Trip Limit, I don't want any error message to appear and they can continue to fill in the sheet.

Please can someone assist with a formula for this. I tried Data Validation to no avail.

Thanks
Kutty

 

[mumps]

Try this macro. Copy and paste the macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your dates in C4 and C5. Then enter a Trip Limit in C2 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    If Range("C4") - Range("C3") > Target Then
        MsgBox ("Check Trip Limit")
        Target.Select
    End If
End Sub

 

[DanteAmor]

Hi @TheKutts,
Try this
- Select C4 cell to validate.
- Open the Data Validation dialog box. For this, click the Data Validation button on the Data tab, in the Data Tools group.
- On the Settings tab of the Data Validation dialog window, select Custom in the Allow box.
- Enter the following formula in the Formula box.


=AND(C4<=IF(C2<>"NA",C2,999999999)+C3,C4>=C3)

- Click OK.

 

[TheKutts]

Thanks

 

[DanteAmor]

Youre welcome. Thanks for the feedback.