In the data validation dialog box, for Allow, choose Custom
In the Formula box, enter:
=OR(B3="N/A",AND(B3=DATE(2000,1,1),B3<=DATE(2099,12,31)) )
where B3 is the address of the active cell.
Manc Shaun wrote:
i had thought of using a list/range but the dates i need to enter run from
01/01/2000 to 31/12/2099 so the list will be huge, any other ideas??
"Dave O" wrote:
One way to do this: on a hidden tab or other remote spot in your
workbook, enter the date range in a column and add the N/A at the
bottom or top. It might look like this:
11/8/2007
11/9/2007
11/10/2007
N/A
Then create a named range called DateRange (or some similar unique
name) and refer to those cells, including the N/A. When you go to set
up the data validation, in the Allow box choose "List" and in the
Source box enter the formula =DateRange.
Will that work for you?
DaveO
Eschew obfuscation
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html