Combination of IF function with Data Validation

[yichuansancun]

Hello -

In the cell that I have created a drop down list, is it possible to add an IF condition in the same cell?

For example, A1 is the cell that contains the drop down list and the drop down list came from range B1:B10.

If A1="Cat", the cell will give me the drop down, otherwise, display "n/a".

Thanks in advance!

Perri

 

[villy]

As I understand youi want to check if the word "Cat is in drop down list otherwise N/A. Why not choose from drop down list itself...

 

[yichuansancun]

No, I want to achieve A1 being dependent on another cell, say A2.

So if A2="Cat", A1 will be a drop down list, otherwise, A1 should display "n/a".

Does it make sense?

Thanks,
Perri

 

[villy]

Try this...

Sub drop()
For Each c In Range("B20:B25")
    If c.Text = "Cat" Then
            With c.Offset(0, 1)
            .Validation.Delete
            With .Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B$2:$B$10" ' Change this range with your data of your drop down liist
            End With
        End With
    End If
Next
End Sub

 

[yichuansancun]

Sorry, no programming background here

Can any formulas work rather than writing the codes?