Complex Data Validation?

[Cammandk]

I want to set entry into a cell based on any of the following conditions:

1. If A1 = "F","O","E" then no entry allowed
2. If A1 = "I" or "S1" then entry allowed
3. If A1 = "B" then option Defined Name "List1"

I am looking to enter this as a custom formula is DV setting in cell.

Thanks
DK

 

[Luke M]

THe last option is a problem. We can do a custom formula to limit choice to something from List1, but it won't be a dropdown. Is that acceptable?

Assuming B2 is cell with data validation:
=AND(NOT(OR(A1={"F","O","E"})),OR(A1={"I","S1"},ISNUMBER(MATCH(B2,List1,0))))

 

[Cammandk]

Hi Luke
Thanks for this. The list for the last option would be something like "CCJan","CCFeb","CCMar","CCApr",CCMay" - through to "CCDec"

DK

 

[Cammandk]

Hi Luke
Just about to start travelling for a while.
Looking at the formula now where does the selection of "B" in A1 become part of the formula?

DK

 

[Luke M]

You can put the list in a range. I thought you had it in a named range, hence the List1 in the formula. Forgot about the "B", I'll fix that. If your list is in D2:D13, DV formula would be:
=AND(NOT(OR(A1={"F","O","E"})),OR(A1={"I","S1"},AND(A1="B",ISNUMBER(MATCH(B2,D2:D13,0)))))

 

[Cammandk]

Hi Luke
I may be doing this wrong but getting error message that "You may use reference operators or arrays for data validation criteria.
I have selected cell T33 and gone to data validation and copied the code into the custom formula box. (changed B2 to T33).
DK

 

[NARAYANK991]

Hi David ,

The formula needs to be changed slightly :

=OR(AND(OR(A1="F",A1="O",A1="R"),T33=""),OR(A1="I",A1="S1"),AND(A1="B",ISNUMBER(MATCH(T33,D2:D13,0))))

See if this works.

You will need to uncheck the box labelled IGNORE BLANK.

Narayan