Excel Validation Help Please

TheKutts

New Member
Joined
Apr 24, 2019
Messages
9
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top