Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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