Cell value separation as a data for drop down list

[Kiril]

Hello,

Could you help me on creating and formula to solve following issue?

I have column A with following cell values x1;x2;x3 or x1;x2 or x1

where x1 x2 and x3 are integers between (1-99).

I need to create a drop down list in corresponding cells in column B
with following choices:
x1
x2, if exists
x3, if exists


any ideas or hints are welcomed.

Thank you in advance.
Kiril

 

[Nebu]

Hi:

The following macro will do what you are looking for

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    strng$ = Cells(Target.Row, 1).Value
    myArray$ = WorksheetFunction.Substitute(strng, ";", ",")
        With Cells(Target.Row, 2).Validation
            .Delete
            On Error Resume Next
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=myArray
        End With
End If

Application.ScreenUpdating = True
End Sub

Thanks

 

[Kiril]

Thank you Thomas,

But I trying to find non-VBA solution that could be paste in data validation entry or in a column for further list creation.

Thanks

 

[Chihiro]

You'd need helper column(s) for that.

Either do it on the same sheet or on another sheet.

See attached sample using dynamic named range.

 

[Kiril]

Dear Chihiro,

Thank you for such solution. The issue is solved