Creating Drop Down list from multiple sheet range

[somnath6309]

How to create a drop down list from multiple sheet range? Kindly Vide the attachment and reply

 

[Sam Mathai Chacko]

You could loop through each sheet and get the values to be uniquely listed in another range, and then use that as a source. When you say drop-down list, are you referring to the data validation drop-down list in a cell, or a drop-down control?

 

[Luke M]

You'll need to build the list in a single location, in a single row or column. A single list in Data Validation can't be done from multiple locations.

 

[bobhc]

Good day somnath6309

You need to do as Luke M has suggested, for a drop down list, this link may be of help to you..

http://www.contextures.com/xlDataVal01.html

 

[SirJB7]

Hi, somnath6309!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Creating Drop Down list from multiple sheet range - 1_EXTRACT LIST FROM MULTIPLE SHEETS (for somnath6309 at chandoo.org).xlsm

It uses a dynamic named range NameList with worksheet scope for each worksheet different form that with cell having data validation (named range DataValCell) and has code in this worksheet activation event as follows:

Option Explicit
 
Private Sub Worksheet_Activate()
    ' constants
    Const ksName = "NameList"
    Const ksWSName = "Sheet4"
    Const ksDataVal = "DataValCell"
    Const ksComma = ","
    ' declarations
    Dim rng As Range
    Dim I As Integer, J As Long, A As String
    ' start
    A = ""
    ' process
    '  collect
    With ActiveWorkbook
        For I = 1 To .Worksheets.Count
            With .Worksheets(I)
                If .Name <> ksWSName Then
                    Set rng = .Range(ksName)
                    If Not rng Is Nothing Then
                        For J = 1 To rng.Rows.Count
                            If A <> "" Then A = A & ksComma
                            A = A & rng.Cells(J, 1).Value
                        Next J
                    End If
                End If
            End With
        Next I
    End With
    '  assign
    With Range("DataValCell").Validation
        On Error Resume Next
        .Delete
        On Error GoTo 0
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=A
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ' end
    Set rng = Nothing
End Sub

So each time you selects worksheet Sheet4 the data validation list of cell E2 is created according to all names in the other worksheets.

Just advise if any issue.

Regards!