Conditional List

[balusm]

Hi

In a cell I need a conditional formula(?) based on the value of another cell.

If another cell = "yes" then I want it to require user to choose one of the values from a list
If another cell = "no" then I need just to enter text 'N/A' and no offer any list

How can I do this?

It's probably not too complex.

Many thanks

 

[Scott T]

You can use dependent data validation for this.

List the options for when the cell is yes and name that range "yes"
put N/A in a cell and name the range "no"

Select the cell you want this user change and in data validation choose list and as the source use, where E1 is the anther cell that is yes or no.

=INDIRECT(E1)

When the other cell is Yes the list will be you options and when it is no the only option will be N/A

 

[balusm]

Hi Scott

I can get the conditional lists working but what I'm trying to achieve is that if the chosen value is 'No' to automatically populate the cell with 'N/A', rather than put a dropdown there with only 'N/A' that still needs to be chosen.

Is that possible?

 

[Scott T]

How is the Yes or No put into the cell? By formula result or entered by the user?

 

[balusm]

Chosen by the user via a dropdown (Yes or No).

 

[Scott T]

Right click on the tab and select view code. Past this and change cell references as need. Replace opt1,opt2,opt3 with your list

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1")) Is Nothing Then
    If UCase(Range("E1")) = "NO" Then
        Range("A1") = "N/A"
    Else
        With Range("A1")
            With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="opt1,opt2,opt3"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
            End With
    End With
    End If
End If
End Sub