Combine 2 (or more) named ranges into one continous named range

[mc-lemons]

Hi all,

I have searched the forum and the internet, but I’m not finding the solution I’m after. I feel that one of you experts know of a way to do this. I am trying to do this without VBA.

Let’s say I have 2 dynamic named ranges: Range1 and Range2.

And at this point in time, this is what the ranges contain:

Range1	Range2
a	g
d	h
c	i
d	j
e	k
f	l

<tbody>
</tbody>

Since both ranges are dynamic, the data above could change at anytime, of course. But I needed somewhere to start for this example.

I want to created a third named range, let’s call it Range1&2 (which would inherently be dynamic as well, since Range1 & Range2 are dynamic).

Range1&2 would then contain the following data:

Range1&2

a

d

c

d

e

f

g

h

i

j

k

L

<tbody>
</tbody>

Range1&2 could then be used for Data Validation lists, with the index, count, sum functions, etc.

I found these two methods at get-digital-help which essentially accomplish what I’m after:

First there is this one (Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource ):

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))

Or this one, which is cool because it eliminates blanks (Merge two columns with possible blank cells in excel (formula) | Get Digital Help - Microsoft Excel resource):

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))


Note: both are array formulas.


The problem with these is that they only work when input into a cell, and then dragged down. I was looking for method to do this inside a named range, without using any cells. Maybe there is way to adjust the formulas above to do the same. Also, I figured if this could be solved, that any number of named rnages could be combined (I may need to do this shortly down the road)

I’m open to all suggestions and thanks for looking!

 

[venkat1926]

see the sample sheet

Excel Workbook
	A	B
1	a	h
2	s	j
3	d	k
4	f	l
5	g	p
Sheet1

see validation is effected on range F1 to F10 by this macro (I did not understand the formula)

Sub test()
Range("F1").EntireColumn.Cells.Clear
Range("D1").EntireColumn.Cells.Clear
Range(Range("A1"), Range("A1").End(xlDown)).Name = "range1"
Range(Range("B1"), Range("B1").End(xlDown)).Name = "range2"






Range("range1").Copy Range("d1")
Range("range2").Copy Range("D1").End(xlDown).Offset(1, 0)


Range(Range("D1"), Range("D1").End(xlDown)).Name = "range3"


'MsgBox Range("range3").Address


Range("F1").Validation.Delete
    With Range("F1:F10").Validation
        .Add Type:=xlValidateList, Formula1:="=range3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[mc-lemons]

Thanks for taking the time to work out a solution.

However, I am after something a little different. I'm not sure if you noticed on the original post, but I'm after a non-VBA solution (I might not be able to utilize VBA for this project, so I was trying to stay clear to avoid any issues down the road).

I actually already have the formulas worked out to make the first two named ranges dynamic, so that is already figured out. I was more or less just giving some background on the setup when I brought up the dynamic named ranges, so that everyone got a good picture of what is going on.

Also, I want the combined data list (what you called "range3") to be combined only in a named range (i.e. i don't want the combined list to show up in any cells).

I found in my searches the suggestion to put in Range3's "Refers to" box: =Range1,Range2

Say Range1 =$A$1:$A$5

and Range2= $B$1:$B$5

Then back in the Name Manger dialog, if you select Range3 and then click the cursor down in the Refer's box, you do indeed see both Range1 *& Range2 highlighted on screen. However, if you then try to create a drop down data validation list from Range3, it won't work.

So the above suggestion for combining ranges doesn't work for my needs.