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
datavalidation, 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

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
datavalidation, 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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=550168

Use datavalidationallowcustom 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"
wrote in message
news:edwardpestian.293wro_1149809104.4559@excelfor um-nospam.com...

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
datavalidation, 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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=550168