Data Validation
Posted by Aoife on December 03, 2001 8:47 AM
I'm trying to restrict data in a cell so that it will either be a date or a single text entry "Not Reqd". however, I cannot specify both dates and text using the data validation function. Does anyone know a way around this?
Posted by JAF on December 03, 2001 8:57 AM
I think the only way you can do that is to have use the "List" validation option and create a list containing all potential valid dates as well as your "Not Reqd." text string.
Not the most elegant of solutions, but it works!
JAF
Posted by Aoife on December 03, 2001 9:11 AM
thanks Jaf, I'd thought of this, but the date could be conceiveably any date in the future, and this will be required in a multitude of different workbooks which would require a lot more work than would justify validating the cell.
Posted by lenze on December 03, 2001 11:08 AM
Not Perfect, but this will work.
Say your cell to validate is A8
Use custom and enter this formula
=OR(A8="Not Reqd",N(A8)>37226)
The N function returns the serial number of a cell when a date is entered. The formula above requires a date be entered greater than 37226(Dec 1,2001) You can adjust if necessary. You could also stipulate that N(A8) fall between two dates
