How to validate

I need to validate an entry against a range of dates. For example, if the user entered a date, that was not in the range E9:E39 the would get a specified message: "Date is not found in the range." I tried using data>validation, but it would not accept the range.

Thanks in advance.

ep

That should work. Did you choose "List" for the Allow Field? When you enter
your range, did you start it with an = sign?

=E9:E39 will look in that range.

E9:E39 will only accept the literal value "E9:E39"

HTH,
Elkar


"edwardpestian" wrote:

>
> I need to validate an entry against a range of dates. For example, if
> the user entered a date, that was not in the range E9:E39 the would get
> a specified message: "Date is not found in the range." I tried using
> data>validation, but it would not accept the range.
>
> Thanks in advance.
>
> ep
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=550168
>
>

Unfortunately it is providing a drop down menu which is what I don't want.

Regards,

ep

Use data>validation>allow>custom and put in this formula

=ISNUMBER(MATCH(A1,$E$9:$E$39,0))

then type in the type of message you want under the error alert tab

this example assumes that A1 is the cell the user types the date into

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to validate an entry against a range of dates. For example, if
> the user entered a date, that was not in the range E9:E39 the would get
> a specified message: "Date is not found in the range." I tried using
> data>validation, but it would not accept the range.
>
> Thanks in advance.
>
> ep
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
> http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=550168
>

Ended up placing this formula on a hidden admin sheet that I already had, and used the indirect function under data validation.

=NOT(ISERROR(MATCH(G6,E16:E39,0)))

Thanks for all the help.

ep