| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate dynamic ranges
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate dynamic ranges
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 "RD Wirr" wrote in message ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data validation: concatenate two separate ranges in the List? | Excel Discussion (Misc queries) | |||
| Concatenate a reference to named ranges in other workbook | Excel Worksheet Functions | |||
| Concatenate function should accept cell-ranges | Excel Discussion (Misc queries) | |||
| How do I Concatenate these Dynamic Rows | Excel Worksheet Functions | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Linear Mode
