Data Validation - Same Cell with 2 Different Criteria

[No.Solutions.]

Hello All,

What I'm trying to accomplish:
If the cell B1 is populated with "No" then I want the cell A1 to remain as an Any Value option.
If the cell B1 is populated with "Yes" then I want the cell A1 to be a Data Validation List option based on the range named "Apples".

I can get the List option no problem but I cannot seem to get it to keep the Any Value status at all! I'm thinking I may need to delve into a macro to accomplish this goal but I'm not really sure and am hoping to avoid it if at all possible.

Thanks for your assistance
No.Solutions.

[No.Solutions.]

I fixed the macro I've been working on to accomplish the desired result but I would still prefer a solution with no macro if at all possible.

Here's the temporary fix until I can find if it's possible without the macro:

Sub Test()

If Range("B1") = "Y" Then
        Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With


End If

End Sub

[Whizbang]

Set data validation to custom and enter this formula

=IF(B1="No", True,IF(ISERROR(MATCH(A1,Apples,0))=FALSE,True,False))

You could also use

=IF(OR(B1="No",ISERROR(MATCH(A1,Apples,0))=FALSE),TRUE,FALSE)

[No.Solutions.]

Thanks Whizbang, unfortunately when I entered your formulas into the custom section of Data Validation they did not return anything

[Whizbang]

Do you mind clarifying what you mean? What are you expecting to be "returned"? Try entering a value into cell A1. If B1 = "No" you will be able to enter any value. If B1 = "Yes" and the value in A1 does not match any value in "Apples" then you will receive an error message.

Unless you deselected the "Show error alert after invalid data is entered" option.

Another possibility is that your "Apples" range is not spelled the same or something.

[No.Solutions.]

I looked at your formula again and it does work to your specifications; unfortunately the end result that I need would be that if B1 = "No" then any value could be entered (which you have achieved). If B1="Yes" then the cell turns into a dropdown list using the name range of Apples.

[shg]

In data validation for A1, tick Ignore blank and In-cell dropdown, Allow: List, Source: =IF(B1="No", Blank, Apples)

... where Blank is a single-cell named range with nothing in it.

[Whizbang]

Ah. I misread the original post.

Thank you, shg. I learned something new.

[No.Solutions.]

Sweet! Thanks for both of your help! Works awesomely!