Data validation

Damo10

Active Member
Joined
Dec 13, 2010
Messages
460
Hi,

I have a workbook that a other people enter data in, there are issues if they dont add the correct data.

Is it possible to have data validation check to see if a valid date has been entered or "N/A" as these are the only 2 things that should be entered?

I can do the data validation for the date but dont know how to incorporate the "N/A"

Regards,

Damian
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

I take it that your data validation rule uses a formula to check whether or not a date has been entered.

Perhaps you can incorporate Excel's ISNA function into your formula to check if it contains the #N/A error value...
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,320
Office Version
  1. 2010
Platform
  1. Windows
I can do the data validation for the date but dont know how to incorporate the "N/A"
Why don't you show it to us then so we know what it is that needs to be modified and/or supplemented?
 

Damo10

Active Member
Joined
Dec 13, 2010
Messages
460
Hi,

I have just been using the Data Validation from the ribbon to select a date greater than the required minimum date, but i would also like the validation rule to accept "N/A" as a input.

Regards,
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
You may try sth like:

Assume A1 is the active cell, In DV,
Allow: Custom
Formula:
=OR(AND(A1>=1,A1<=2958465),A1="N/A")

where 1 is replaceable with a minimum date; 2958465 is the largest date Excel recognizes (31/12/9999)

Nevertheless, coz there are too many "different" habits in inputting date across the globe, suggest you to include guidelines in the Input Message.





Hi,

I have just been using the Data Validation from the ribbon to select a date greater than the required minimum date, but i would also like the validation rule to accept "N/A" as a input.

Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,109,202
Messages
5,527,388
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top