Combine two non-contiguous named ranges in new named range

[Chris Adams]

Hello,

In Combobox2 I would like to show a list, depending on the selection
in Combobox1. This list is based on a dynamic named range (and is
triggered by an Event-procedure).

I would like this named range to combine selected non-contigous
ranges.

Example:

In Column A, one block is called "Assets". Further down in Col A,
another, non-contiguous, block is called "Cash".

I would like to create a named range called "AssetsCash" that combines
both blocks. For instance, I wondered whether it was possible to
create "AssetsCash" = INDIRECT("Assets" & "Cash"). This didn't work
for me.

The reason I am fixated on this solution is that I already have a
large V-lookup table built into the workbook. Combobox2 would provide
a simple item to refer to that V-lookup table. (A sorry alternative
is to build two V-lookup tables, with the second table containing the
ranges contiguously).

I looked in the archives, but couldn't find an answer. Many thanks in
advance if you have such a solution.

Regards,
Chris Adams

 

[Harlan Grove]

...
...

I would like to create a named range called "AssetsCash" that combines
both blocks. For instance, I wondered whether it was possible to
create "AssetsCash" = INDIRECT("Assets" & "Cash"). This didn't work
for me.

Unless you already have a defined name AssetsCash, fedding "AssetsCash" to
INDIRECT correctly generates an error. You need to define the name AssetsCash
referring to =(Assets,Cash). INDIRECT("(Assets,Cash}") will only return Assets.
If you were feeding this to a function that could accept general range
references and returns a single value, e.g., SUM or COUNT, you could try
INDIRECT({"Assets","Cash"}), but it returns something that treated as if it were
an array of range references rather than a multiple area range.

The reason I am fixated on this solution is that I already have a
large V-lookup table built into the workbook. Combobox2 would provide
a simple item to refer to that V-lookup table. (A sorry alternative
is to build two V-lookup tables, with the second table containing the
ranges contiguously).

Do you mean you already have two separate tables but you want to combine them
into a single logical table? You could use a complex INDEX(.,MATCH()) expression
to do this, but not VLOOKUP. If your first tables were named TBL1 and TBL2, the
leftmost column were text, and you wanted the numeric value from the N_th
column, you could use the array formula

=INDEX(IF(ROW(INDIRECT("1:"&(ROWS(TBL1)+ROWS(TBL2))))<=ROWS(TBL1),
N(OFFSET(TBL1,ROW(TBL1)-CELL("Row",TBL1),N-1,1,1)),
N(OFFSET(TBL2,ROW(INDIRECT("1:"&(ROWS(TBL1)+ROWS(TBL2))))-ROWS(TBL1)-1,
N-1,1,1))),
MATCH(C1,IF(ROW(INDIRECT("1:"&(ROWS(TBL1)+ROWS(TBL2))))<=ROWS(TBL1),
T(OFFSET(TBL1,ROW(TBL1)-CELL("Row",TBL1),0,1,1)),
T(OFFSET(TBL2,ROW(INDIRECT("1:"&(ROWS(TBL1)+ROWS(TBL2))))-ROWS(TBL1)-1,
0,1,1))),0))

However, you may find using a user-defined function to stack ranges more
efficient. Here's a simplistic example. It could be embelished to support a
ParamArray argument accepting ranges, arrays and even scalars treated as
degenerate 1-by-1 arrays, but I'm too lazy to code the necessary dimension
checking for arbitrary array arguments.


Function stack(a As Range, b As Range) As Variant
Dim rv() As Variant, r As Range
Dim i As Long, j As Long, k As Long, cmax As Long, rmax As Long

For Each r In a.Areas
rmax = rmax + r.Rows.Count
If r.Columns.Count > cmax Then cmax = r.Columns.Count
Next r

For Each r In b.Areas
rmax = rmax + r.Rows.Count
If r.Columns.Count > cmax Then cmax = r.Columns.Count
Next r

ReDim rv(1 To rmax, 1 To cmax)

For Each r In a.Areas
For i = 1 To r.Rows.Count
k = k + 1
For j = 1 To cmax
rv(k, j) = IIf(r.Column + j < 257, _
r.Cells(1, 1).Offset(i - 1, j - 1).Value, _
CVErr(xlErrRef))
Next j
Next i
Next r

For Each r In b.Areas
For i = 1 To r.Rows.Count
k = k + 1
For j = 1 To cmax
rv(k, j) = IIf(r.Column + j < 257, _
r.Cells(1, 1).Offset(i - 1, j - 1).Value, _
CVErr(xlErrRef))
Next j
Next i
Next r

stack = rv
End Function

 

[Chris Adams]

Many thanks for your time and help Harlan.

I eventually created a workaround, because I wasn't able to get the
ComboBox to accept a named range consisting of combined ranges.

For instance, ComboBox2 properties ListFillRange wouldn't accept the
defined named range Total, given the following parameters:

"First" (a Named Range in the sheet)
Apples
Pears
Oranges

"Second" (a Named Range in the sheet)
Fruit
Vegetables

"Total" (a defined Named Range I created based on the above, using the
formula)
=(First,Second)


So, I simply resorted to creating a third list comprised of the
combined First & Second ranges, and then used this in the ComboBox.

With the Vlookup tables, I resorted to creating a second table that
combined all the Vlookups, and then used the item returned by the
ComboBox in the Vlookup to return an answer from this second table.
Not particularly intelligent or nice, but helped me meet a deadline.
I got the impression from your post that there was probably no simple
way for this.

Many thanks again for your input Harlan - very much appreciated.

Regards,
Chris Adams