Data Validation Question - List or any value

[zherczku]

Hi All,

I try to make a data validation that depeing on the adjacent cell value allows to select from a drop down list (in case of "Employee") or allows to enter any value into the cell (in case of "External").

I tried to use the following formula =indirect(if($A2="Employee"; "NamedRange_Employee" ; "")). It works fine if Employee is selected, but does not work in the other case. The Indirect("") does not allow the "any value selection".


Any suggestion, solution? This should be a simple task, but I was not able to find how to solve...

Thanks,

Zoli

 

[zherczku]

Paste this into Sheet1. Remember to test on a copy of your Workbook as unexpected results may occur.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.ActiveSheet
Set ws2 = wb.Sheets("Employee")

If Left(Target.Address, 2) = "$B" Then
    If Target.Offset(0, -1).Value = "Employee" Then
        With Target
            .Validation.Delete
            .Validation.Add xlValidateList, Formula1:="=Employee!$AA$1:$AA$49"
        End With
    Else
        With Target
            .Validation.Delete
        End With
    End If
Else
    Exit Sub
End If
End Sub

Thanks a lot Skyybot !!!!

Based on this I could complete the task, here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sv As String

    Dim tblcol As ListColumn

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    Set tblcol = ThisWorkbook.Worksheets("Project Resource").ListObjects("DB_ProjectResource").ListColumns("Name")

    If Intersect(Target, tblcol.DataBodyRange) Is Nothing Or Target.Count <> 1 Then
        Exit Sub
    Else
  
        If Target.Offset(0, -2).Value = "Vodafone" Then
            With Target
                sv = "=INDIRECT(""BL_Compl"")"
                .Validation.Delete
                .Validation.Add xlValidateList, Formula1:=sv
            End With
        Else
            Target.Validation.Delete
        End If
    End If
End Sub