Data Validation

M

Manc Shaun

Folks,

i am using excel 2003 and i would like to set a number of cells so that
users can only enter a date or N/A. i can set the date range but not the
N/A...

any ideas
 
D

Dave O

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
 
M

Manc Shaun

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??
 
D

Debra Dalgleish

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.
 
Top