multi validation?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ADK - 19 Jul 2007 21:08 GMT

Is there a way to have a cell validate the following parameters:

1. Ignore blanks
2. Date greater than 1/1/07
3. "NR" value is acceptable

So far I can only do 1 & 2

Reply to this Message

Dave Peterson - 19 Jul 2007 22:10 GMT

Check your other post.

> Is there a way to have a cell validate the following parameters:
>
[quoted text clipped - 3 lines]
>
> So far I can only do 1 & 2

Signature

Dave Peterson

Reply to this Message

ADK - 20 Jul 2007 12:30 GMT

Sorry, first post is not visable for some reason....

> Check your other post.
>
[quoted text clipped - 5 lines]
>>
>> So far I can only do 1 & 2

Reply to this Message

ADK - 20 Jul 2007 12:46 GMT

I tried resetting the newsgroup (in Outlook) but I still can not see my
other posting.

Can you transfer your post to this one?

Thanks

> Check your other post.
>
[quoted text clipped - 5 lines]
>>
>> So far I can only do 1 & 2

Reply to this Message

Dave Peterson - 20 Jul 2007 12:55 GMT

You can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

> I tried resetting the newsgroup (in Outlook) but I still can not see my
> other posting.
[quoted text clipped - 16 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

ADK - 20 Jul 2007 13:06 GMT

Found it actually at
http://www.tech-archive.net/Archive/Excel/microsoft.public.excel/2007-07/msg0093
0.html

Thanks for the help....the validation works great

> You can use google (maybe a few hours behind) to search for stuff you've
> posted (and find the replies, too)
[quoted text clipped - 22 lines]
>> >
>> > Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Jul 2007 22:22 GMT

> Is there a way to have a cell validate the following parameters:
>
[quoted text clipped - 3 lines]
>
> So far I can only do 1 & 2

Select the cell or cells the validation is to apply to and then click on
Data/Validation in Excel's menu. Next, select "Custom" in the Allow drop
down and put this formula in the Formula field...

=AND(ISNUMBER(D1),OR(D1>=DATE(2007,1,1),D1="NR"))

where you will substitute the top, left cell address of your range for the
D1 that I used in the above formula. Then click OK.

One note about the formula... it will allow any number whose value is equal
to or greater than the equivalent serial value for January 1, 2007. This
means any **floating point** number between 39083 (date - 1/1/2007) and
2958465 (date - 12/31/9999) will pass the validation test.

Rick

Reply to this Message

ADK - 20 Jul 2007 12:43 GMT

The date part works but it will not accept NR

I changed D1 to the cell I was validating (Q7)

Using Excel 2000 if that matters

Any other ideas?

>> Is there a way to have a cell validate the following parameters:
>>
[quoted text clipped - 19 lines]
>
> Rick

Reply to this Message