Combine arbitrary length 1d vertical arrays

[ds9anderon]

I have ten arrays in columns A through J starting at row 3 of arbitrary length. They are all specified with named ranges, which are in turn specified in a single named range. These arrays contain only text values. How might I combine them into column N. Note that given the named range conditions the named range will either be equal to all non-empty values within the array, or an empty array. I need only non-empty values. I need to do this only with formulas if possible.

I have started by using the index function and row function and can return the appropriate values from a single area, however I do not know how to have it look to the next area once the last value in the previous area is reached.

 

[ds9anderon]

Here is an example. Where NR is a named range containing NR1 and NR2. NR1 and NR2 are named ranges containing NR1_1 etc. NR1_1 etc. contain the values listed below them. I need a formula which results in what is shown in column N and can accept NR1_1 through NR2_5 being of arbitrary length.

 

[NARAYANK991]

Hi ,

I am somewhat skeptical about this requirement ; why do you want it done this way ?

You mention :

I need to do this only with formulas if possible.

What if it is not possible ?

VBA makes the whole job so easy , to me it is a waste of time trying to find an alternative.

If you don't mind VBA , see this link :

http://www.get-digital-help.com/201...into-a-single-range-while-eliminating-blanks/

Narayan

 

[ds9anderon]

I am comfortable with VBA and the file which this is going in already uses VBA extensively. I am hoping to avoid it for this particular purpose because the named ranges mentioned use an if statement to determine whether or not they should be included in the final range, which is then used as a listfillrange for a listbox on a different sheet. The if statement changes based on a bunch of different cell values which change often and I would prefer not to call a function each time the cells change.