concatenate dynamic ranges

[RD Wirr]

I have some dynamic named ranges, the contents of which, I need to
concatenate to create a new range. Then I need to do a vlookup on the
resulting range. I also need to use the new range in a validation dropdown
list. The original ranges are defined using something like
=OFFSET($A$1,0,0,COUNTA($A:$A),2). I can join two ranges together in the
‘Define Name’ box referring to =Range1,Range2 and excel seems to recognize
the two non-contiguous ranges but the range doesn’t work in a formula or
list. The two original ranges are the same width but dynamic length.

Thanks in advance,
RDW

 

[Bernie Deitrick]

RDW,

No need to join the ranges. Instead of VLOOKUP, try using

=INDEX(RANGE2,MATCH(Value, RANGE1,FALSE))

HTH,
Bernie
MS Excel MVP

 

[RD Wirr]

Hi Bernie,

Thanks for the input but I can’t make this work. I only get an #NA.

Maybe I should explain better.
I have two ranges that look something like this:
Range1
A B
1 1 a
2 2 b
3 3 c

Range2
E F
10 5 e
11 6 f

I need to do two things. I need to get a complete listing of the data in the
ranges in A1:A3 and in E10:E11 to use as a validation list. I also need to
access the larger ranges A1:B3 and in E10:F11 to do a lookup on the data in
B1:B3 and F10:F11 using the criteria from the validation list.

I think I mentioned, the actual ranges I am using are dynamic.

Can you give me another hint?

Regards,
RDW

 

[Bernie Deitrick]

OK, I think I know what you are doing now.

You cannot use a union to create the Validation list - you need to use formulas to put the two lists
together. With Range1 and Range2 being your named ranges, enter this and copy down until you get ""
values:

=IF(ROW(A1)<=COUNTA(Range1),INDEX(Range1,ROW(A1)),IF(ROW(A1)<=(COUNTA(Range1)+COUNTA(Range2)),INDEX(Range2,ROW(A1)-COUNTA(Range1)),""))

Then use that list for your data validation.

You could also use the above technique to pull the second column into the table to use for the
VLOOKUP, or you could use this - if the first range doesn't have what you want (you get the NA
error) then the second range should. If neither range has the value, then you will get an error:

=IF(ISERROR(VLOOKUP(Value,Range1,2,False)), VLOOKUP(Value,Range2,2,False),
VLOOKUP(Value,Range1,2,False))


HTH,
Bernie
MS Excel MVP

 

[RD Wirr]

Hi Bernie,

Thanks for sticking with this one. I appreciate the help.

The first good clue is that I have to take the validation list from a column
of cells. But I am still having trouble with your combining formula. auditing
the formula I am getting a #REF error from the Index function in the formula.
BTW, my ranges are not in A1, and I also tried putting this formula in A1
with the same results. I think we're close.

Thanks,
RDW

 

[Bernie Deitrick]

RDW,

Try this one - I had used single column named ranges, and multi-column named ranges require a
different tack.

To get the first column:
=IF(ROW(A1)<=ROWS(Range1),INDEX(Range1,ROW(A1),1),IF(ROW(A1)<=(ROWS(Range1)+ROWS(Range2)),INDEX(Range2,ROW(A1)-ROWS(Range1),1),""))

To get the second column:
=IF(ROW(A1)<=ROWS(Range1),INDEX(Range1,ROW(A1),2),IF(ROW(A1)<=(ROWS(Range1)+ROWS(Range2)),INDEX(Range2,ROW(A1)-ROWS(Range1),2),""))

I hope you can see the required changes ;-)


HTH,
Bernie
MS Excel MVP

 

[RD Wirr]

Hi Bernie,

That one worked. Thanks very much. The only problem is, it really slows
things down. I have several columns of these lookups that are each 400-500
rows deep and the sheet is running very slow. I don't suppose you know way to
do this programatically of some other faster method...

Regards,
RDW

 

[Bernie Deitrick]

It is easy to write a macro to create a third range from two named ranges - you can use the
workbook's worksheet change event - this will add any new entry from Range1 or Range2 to Range3 (if
it isn't already in Range3). It doesn't handle deletions - but it could.... just a few lines of
code extra...


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myC As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("Range1")) Is Nothing And _
Intersect(Target, Range("Range2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Set myC = Range("Range3").Find(Target.Value)
If myC Is Nothing Then
Range("Range3").End(xlDown)(2).Value = Target.Value
End If
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP