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



"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

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