Basic data validation question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bingo Accent - 07 Mar 2007 14:34 GMT

I can't believe that I can't figure this out for myself, but...

I want to use Data Validation to only allow entry to a cell, if the
content of another cell is a specific text.

i.e. Cell E5 has a status of 'Open' or 'Closed' (data entry
validated). Cell F5 shows 'Date Closed'.

I want to prevent someone entering a date in the 'Date Closed' cell,
if E5 shows 'Open'.

I've tried using Data Validation>Custom and entering variations on
=IF(E5="Closed"), but I'm getting nowhere.

Please help!

Thanks.

Reply to this Message

ed@HelpExcel.com - 07 Mar 2007 14:47 GMT

You could use some VBA to accomplish this.

Regards,
Eddie
http://www.HelpExcel.com

Reply to this Message

KC Rippstein - 07 Mar 2007 14:53 GMT

The easiest way to accomplish this is to follow Debra Dalgleish's
instructions here:
http://www.contextures.com/xlDataVal02.html
She has some links at the top of that page to other ways of doing it, but
the one presented on that page is the cleanest.
-KC

>I can't believe that I can't figure this out for myself, but...
>
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message

Bingo Accent - 08 Mar 2007 10:33 GMT

I knew it was easy!

A colleague came up with =E5="Closed" and it works.

Reply to this Message

Ragdyer - 07 Mar 2007 23:31 GMT

This seems to work if "Date Closed" is entered in E5 while F5 is either
blank or contains "closed":

Click in E5, and <Data> <Validation> <Custom>, and enter:
=AND(E5="Closed",F5>0)

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> I can't believe that I can't figure this out for myself, but...
>
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message