Conditional Data Validation

[smitpau]

Hi,

This is a slightly different type of conditional data validation request, is it possible use an IF statement where only if it's met a data validation box will appear showing a named range. For example if A1=Yes, A2 is a data validation of named range, x, otherwise it's just a normal cell.

I've tried with the custom criteria but had no luck so far.

Hope I've explained that clearly.

Thanks,

Paul

 

[nardagus]

Hi,

Imo it's not possible to set DV the way you described without using macro.
For that you'd need to place this code in a desired worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Target.Value = "Yes" Then
        With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$2:$B$15"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Else
        With Range("A2").Validation
            .Delete
        End With
    End If
End If
End Sub

This adds List type DV if changed cell is A1, and its value is YES.
If changed cell is A1, but its value is other than Yes it removes DV.
Other cells aren't checked.

 

[jasonb75]

Without using vba you could use this formula to set up the validation list.

=IF(A1="Yes",namedrange,A2)

Which will validate A2 against itself if A1 is anything other than "Yes".

 

[smitpau]

Thanks they're both helpful.

I may have been after an impossible solution if macro's aren't used as the methdod @jasonb75 mentions work but it still shows a dropdown icon but that's probably unavoidable if a custom DV is used, so will use that macro approach.

 

[nardagus]

You're welcome