Data Validation - Multiple Options
 

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

on the data validation chose for a list, define the range, and there put the
dates and N/A

hth
regards from Brazil
Marcelo

"LPS" escreveu:

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".

--
LPS


"Marcelo" wrote:

on the data validation chose for a list, define the range, and there put the
dates and N/A

hth
regards from Brazil
Marcelo

"LPS" escreveu:

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

hi,

My sugestion is:

on an auxiliar column Z for eg. type on rows
1 - N/A
2 - 01/01/06
3
4
366 - 12/31/06

then difine the data validation list z1:z366

the final user could type any date between this range (jan-01 Dec31) or N/A
or still select on the list.

it works here.

hth
regards
Marcelo



"LPS" escreveu:

Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".

--
LPS


"Marcelo" wrote:

on the data validation chose for a list, define the range, and there put the
dates and N/A

hth
regards from Brazil
Marcelo

"LPS" escreveu:

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

Thanks Marcelo. That is certainly worth a try. I appreciate your help.
--
LPS


"Marcelo" wrote:

hi,

My sugestion is:

on an auxiliar column Z for eg. type on rows
1 - N/A
2 - 01/01/06
3
4
366 - 12/31/06

then difine the data validation list z1:z366

the final user could type any date between this range (jan-01 Dec31) or N/A
or still select on the list.

it works here.

hth
regards
Marcelo



"LPS" escreveu:

Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".

--
LPS


"Marcelo" wrote:

on the data validation chose for a list, define the range, and there put the
dates and N/A

hth
regards from Brazil
Marcelo

"LPS" escreveu:

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

Thanks for the feedback

Marcelo

"LPS" escreveu:

Thanks Marcelo. That is certainly worth a try. I appreciate your help.
--
LPS


"Marcelo" wrote:

hi,

My sugestion is:

on an auxiliar column Z for eg. type on rows
1 - N/A
2 - 01/01/06
3
4
366 - 12/31/06

then difine the data validation list z1:z366

the final user could type any date between this range (jan-01 Dec31) or N/A
or still select on the list.

it works here.

hth
regards
Marcelo



"LPS" escreveu:

Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".

--
LPS


"Marcelo" wrote:

on the data validation chose for a list, define the range, and there put the
dates and N/A

hth
regards from Brazil
Marcelo

"LPS" escreveu:

I'm using Excel 2000 in Windows 2000. I would like to create a data
validation which allows 2 options: the first is a date that the user enters
and the second is the characters "N/A" if a date is not relevant. I know how
to restrict the entries to just dates or just text. My question is: Is it
possible to have both (or more than one) validation criteria at the same time
(as described above).?

Many thanks,
--
LPS

Data Validation - Multiple Options
 

Select the cells in which you want the data validation
From the Allow dropdown, choose Custom
In the formula box, type the following formula, refering to the active
cell (B2 in this example):
=OR(B2="N/A",AND(B2=DATE(2006,1,1),B2<=DATE(2006,12,31)) )
Click OK

LPS wrote:
Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation - Multiple Options
 

That is brilliant. Thank you very much.
--
LPS


"Debra Dalgleish" wrote:

Select the cells in which you want the data validation
From the Allow dropdown, choose Custom
In the formula box, type the following formula, refering to the active
cell (B2 in this example):
=OR(B2="N/A",AND(B2=DATE(2006,1,1),B2<=DATE(2006,12,31)) )
Click OK

LPS wrote:
Thanks Marcelo, but that won't work... I guess I didn't explain fully. I
need the users to enter directly into the cell any one date out of a specific
year, as opposed to picking a date from a list. For example, the date could
be anything between 1 Jan/06 to 31 Dec/06. I have created a data validation
which restricts the cell entry to a date between those two but I also need
the user to be able to "over-ride" the date with "N/A", if circumstances
require it. I could leave the cell without any validation but that leaves
the cell open to anything being entered into it and I need to restrict it to
either any date within the given year or the characters "N/A".



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation - Multiple Options
 

You're welcome! Thanks for letting me know that it helped.

LPS wrote:
That is brilliant. Thank you very much.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html