IF AND / OR Statement for Data Validation rules

Hi Everyone - this is my first post and it is a pleasure to now be a part of your community. I am a bit stuck however with a formula and I hope someone can help me!

I am trying to set a data validation rule based on one cell meeting one either of two conditions, and another cell not meeting a condition for the error message to display but I am having no luck. I have used IF / OR / AND statments before in general excel but the logic seems to be different in data validation custom formulas?

When cell I26 is selected for input I want an error message to display when one of either two conditions are met in cell C21 (in this case "Lifetime C", or 'Lifetime F"), and when cell F11 does not equal a particular date.

I tried this and other formuals but none seem to work: =and(f11<>datevalue("31/12/2030"),or(C21="Lifetime D & O Commercial",c21="Lifetime D & O Financial))

I have also tried 'dumbing down' the logic to meet only one condition in cell C21 and that didn't work either...

Appreciate in advance any assistance anyone can give.

Thanks

Derek

This is incredibly newbie of me, as it is my second post here: but have you considered the NOT function?

Hi there

No I haven't in what context would you apply it in this case?

http://office.microsoft.com/en-us/excel-help/not-function-HP010062402.aspx]Try this page. Do you think it's what you're looking for, in regards to "another cell not meeting a condition for the error message to display"?

Thanks for link but that doesn't really provide a result as far as I can see. Cell F11 only comes into play when one of either two conditions are met in cell C21.

Cheers

This is the dumbed down version with cell C21 needing to meet only one condition and it still doesn't work - the error message is displayed regardless!! =IF(AND(C21="Lifetime D & O Commercial"),F11<>47848).The date has been converted to general format is this version.

Any ideas?

Hi benishiryo - I visited your fine city recently and had a wonderful time (our regional office is located there...)

Yes I redacted the text somewhat but the formula is the full version and thank you so much for the time and effort you have made! Basically if 'Lifetime C' OR 'Lifetime F' condtion is met AND the date in cell F11 IS NOT 31/12/2030 then the error message should be displayed. Cell F11 is only checked if cell C21 is 'Lifetime C' OR 'Lifetime F'.

I hope I am explaining this properly - forgive me if not. Happy to provide you with more info if you need it.

Cheers

Derek

Derek

Thanks benishiryo. I tried this formula which is a slight variation to your second solution and it did not work either.

=OR(AND(C21="Lifetime D & O Commercial",C21="Lifetime D & O Financial"),F11<>DATEVALUE("30/12/2030")).

OK so cell C21 is a drop down list of product types and cell F11 is the expiry date of the product which is manually input by the user. There are two products, 'Lifetime C' and 'Lifetime F' that must always have an expiry date of 31/12/2030 so I want the error message to show when another cell has been selected (in this case the $ input cell I26) since the product and expiry date cells have already been selected / input. It is only this scenario that the error message applies to. Hope this helps as I am finding it very frustrating... and thanks for all your help!

I will definitely Mark as Solved when I have worked it out... Thanks again

Derek