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

 

[Skyybot]

Adjacent in which direction?

 

[zherczku]

Adjacent in which direction?

just the neighbour cell on the left

 

[Skyybot]

Where is the Employee list coming from? Is it Static or Dynamic?

 

[zherczku]

Simple data validation.

The issue is not with the adjacent cell. The main question is whether it is possible to allow in a data validation rule (1) drop down list and (2) any entry. All this depending on the adjacent cell value.

There must be a solution or a workaround at least....

 

[Skyybot]

I asked because it sounds like you want an Employee Name list only to be available if the adjacent cell equals "Employee". There has to be a list to populate Employee Name. It can be a Range, or a specific list that has that is typed in the Data Validation Rule. If the adjacent cell "External" in it, you just want someone to be able to type something in it. Is this a correct assumption? What if the adjacent cell has nothing in it, or has any other value than Employee or External? Or, is the adjacent cell itself a dropdown with only those two options?

 

[zherczku]

I asked because it sounds like you want an Employee Name list only to be available if the adjacent cell equals "Employee". There has to be a list to populate Employee Name. It can be a Range, or a specific list that has that is typed in the Data Validation Rule. If the adjacent cell "External" in it, you just want someone to be able to type something in it. Is this a correct assumption? What if the adjacent cell has nothing in it, or has any other value than Employee or External? Or, is the adjacent cell itself a dropdown with only those two options?

Yes, the adjacent cell is a drop-down with only two options. Employee and External

And yes, for the Employees I have a database (table), while the External is someone named and filled in by the project manager

 

[Skyybot]

Great. What is the Range of Employee list?

 

[zherczku]

Great. What is the Range of Employee list?

AA1:AA49 on sheet "Employee"

 

[Skyybot]

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