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 doesnt work in a formula or
list. The two original ranges are the same width but dynamic length.

Thanks in advance,
RDW

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" wrote in message
...
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

Hi Bernie,

Thanks for the input but I cant 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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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(Rang e2,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(Rang e2,ROW(A1)-ROWS(Range1),2),""))

I hope you can see the required changes ;-)


HTH,
Bernie
MS Excel MVP


"RD Wirr" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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