Excel Data Validation on selection of values in one other cell

[JABurger]

Hi

I have a list in A1 (New, Quoted, Won, Lost, cancelled) etc. In A2 I want to enter the date this quote has been closed. It must be validated by looking at A1 and can only accept the date if A1 is either Won, Lost or Cancelled. I tried the =And(A1="Won",A1="Lost",A1="Cancelled") and replaced the AND with OR but it doesn't work. Please assist.

 

[FormR]

replaced the AND with OR but it doesn't work.

Hi, this works for me using custom data validation on cell A2 with the "Ignore Blank" checkbox not checked.

=OR(A1="Won",A1="Lost",A1="Cancelled")

 

[JABurger]

Thank you. Seems like the Regional list seperator set as ; created the issue. I set it to , and now it works.