Named Ranges - Non Continuous Cells

[xl_psychic]

Hi Guys,

A quick one for you all.

I have entries in 3 cells, viz: D6,D7 and G13. I want all of the three to be included in a named range which I will be using as a drop down validation on another sheet.

Can this be done.

 

[Aladin Akyurek]

Insert the following code as module into your workbook:

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
    ' Code: Juan Pablo González
    ' Spec: Aladin Akyurek
    ' May 4, 2003
    ' Ref: http://makeashorterlink.com/?P20022174
    ' Mod: Nov 3, 2003, to reduce number of ReDim Preserve calls.
    Dim TempUnion() As Variant
    Dim i As Long, Itm As Variant, Ctr As Long
    ReDim TempUnion(1 To UBound(Arg) - LBound(Arg) + 1) As Variant
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                If Ctr > UBound(TempUnion) Then
                    ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
                End If
                'ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            If Ctr > UBound(TempUnion) Then
                ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
            End If
            'ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
    If Ctr< UBound(TempUnion) Then
        ReDim Preserve TempUnion(1 To Ctr) As Variant
    End If
    ArrayUnion = TempUnion
End Function

Insert a new worksheet and name it Admin...



Let the values in D6, D7, and G3 on Sheet1 be: Don, Jim, and Damon.

Admin

Book4
	A	B	C	D
1	3
2	Don
3	Jim
4	Damon
5
6
Admin

Activate Insert|Name|Define.
Enter List as name in the Names in Workbook box.
Enter the following in the Refers to box:

=arrayunion(Sheet1!$D$6:$D$7,Sheet1!$G$13)

Click OK.

In A1 on Admin enter:

=COUNTA(List)

In A2 enter & copy down:

=IF(ROW()-ROW($A$2)+1<=$A$1,INDEX(List,ROW()-ROW($A$2)+1),"")

Activate Insert|Name|Define.
Enter SList in the Names in Workbook box.
Enter the following in the Refers to box:

=Admin!$A$2:INDEX(Admin!$A:$A,MATCH("*",Admin!$A:$A,-1))

Click OK.

Now you can use SList as source in any appropriate cell in your workbook you want to data validate.

 

[Chas17]

I amy have missed something, Why can't you Select the cells D6,D7 and G13 while holding down the CNTRL Key and then give it a Name?

 

[Zack Barresse]

you can't name non-contiguous cells.

 

[Chas17]

I just did it. Try

 

[Aladin Akyurek]

you can't name non-contiguous cells.

Yes, you can, but the name would be useless as source in data validation. Hence the why of Rohan's post...

 

[Zack Barresse]

Chas17, sorry, didn't clarify well enough.

Aladin, thanks.

 

[Chas17]

I see, works to name range but can not be used in Data Validation as Source for List.

Thanks,
Chas

 

[Chas17]

The complexity of this code Eludes me, i.e. with only three cells, why not on Admin sheet:
A2 =Sheet1!D6
A3 =Sheet1!D7
A4 =Sheet1!G13

Name the range A2:A4 on Admin and use it to validate?

Thanks,
Chas