Input Field Required depending on value of other field

[Frank Bacchus]

Hi:
In a cell where that data is entered, I want to check the value, and if it is a specific value I would like the user to enter additional information in another cell. So based on the "entered" data I want the user to enter a date in another field. In cell A1 the user enters "Waiting on DCS" in cell B1 I want to force the user to enter a valid date. How can I do this with a macro..not VB.? Thanks.

frank

 

[Chihiro]

I'm a bit confused. Macro and VBA is basically same thing. Are you looking for solution using data validation?

What do you consider valid date? Is 1/1/1900 valid? Or does it need to be in some specific date range?

 

[Frank Bacchus]

I'm a bit confused. Macro and VBA is basically same thing. Are you looking for solution using data validation?

What do you consider valid date? Is 1/1/1900 valid? Or does it need to be in some specific date range?

Thanks, Chihiro, understood about Macro and VBA (I meant a formula), A valid date would from 1/1/2017 and cannot be a future date. It is a past date and includes today. Does that help? Thanks

frank

 

[Chihiro]

There's several ways you can go with this. However, without VBA/Macro you can't really FORCE user to enter data.

1. Data validation. Enter following formula for custom validation. Assuming that you want blank unless "Waiting on DCS" is entered in A1.
=OR(AND(A1="Waiting on DCS",B1>=DATE(2017,1,1),B1<=TODAY()),AND(A1<>"Waiting on DCS",B1=""))

2. Use helper column (C). To flag if info is missing in B1.
=IF(A1="Waiting on DCS",IF((B1<=TODAY())*(B1>=DATE(2017,1,1)),"Good","Enter Valid date in Column B"),IF(ISBLANK(B1),"Good","Unnecessary info"))

Or you can simplify formula and use conditional format to flag when valid date isn't present in B1.