Data/Cell Validation

[peazmo]

Hello Guys,

I got these problem with validation:

1. I have a DATE in A1 and I want to enter another DATE in B2 using validation (or formula??) such that (a.) If A1 is empty, prompt the message "Enter Planting Date first", and (b) if the date entered is less than (or before) the date in A1 prompt the message "Harvest date must not be less than the stocking Date". [Imagine: Planting and Harvesting ]

2. I have values in A1:C1. I want to use validation in D1 such that if ANY cell in A1:C1 is empty prompt the message "Complete A1-C1 first".

Please help if you know formulas/validation. Thank you in advance.

 

[p45cal]

re (1):
Data validation (DV) allows only 1 message, so it needs to cover both conditions, something like:
"There should be a stock date in A1 and it should be before the harvest date (or this harvest date should be after the stock date)."

Then in Data Validation of cell B2, choose Custom, and a formula like:
=AND(LEN(A1)>0,B2>A1)
and importantly, the Ignore Blank checkbox should be unticked (clear).
When designing the formula, I put the formula in another cell in the spreadsheet and develop it there (it's looking for True/False where False is rejected by DV), so you can easily add to the complexity, and test, the formula.
re: (2)
use a custom formula such as:
=COUNTBLANK(A1:C1)=0

 

[Peter_SSs]

Edit: Sorry, hadn't seen previous response when I was composing mine.

a) Without getting in to vba code, you will not be able to have two separate messages for cell B2. However, you could see if this is sufficient for B2

On the Data Validation Settings tab
Allow: Custom
Ignore blank: Ensure this is not checked
Formula: =AND(A1>0,B2>=A1)
With a message something like this on the Error Alert tab: "Planting date must be entered first and Harvest date must not be less than Stocking date"

b) For D1
Allow: Custom
Formula: =COUNTA(A1:C1)=3
Error Alert: "Complete A1-C1 first"

 

[peazmo]

Thank you Sir. It's amazing...