Calculating a Dynamic Array across columns

[John Bates]

Hi,
I'm trying to calculate a dynamic array that will include values from three columns that are not next to each other. I'm currently using the following formula for my AE column: =OFFSET(Controls!$AE$5,0,0,COUNTA(Controls!$AE$5:$AE$100))

I just need to use a similiar formula that will also grab values from $W$5:$W$5, $AA$5:$AA$100, and $AE$5:$AE$100.

THANKS!!!
John

[olasa]

Depend of what the next step is with the 3 ranges...

Range1: $W$5:$W$5 ... looks static
Range2: $AA$5:$AA$100
Range3: $AE$5:$AE$100

Option1:
=SUM( $W$5 + OFFSET(Controls!$AA$5,0,0,COUNTA(range2)) + OFFSET(Controls!$AE$5,0,0,COUNTA(range3)) )

Option2:
Use an indicator column and do a =SUMIF() or use a Pivottable

//Ola

[kwilleitner]

Olsa, it looks like he wants to take three different lists and append them together or a single list. So, one list would have {"a";"b"} and the second would be {"c";"d"} and he wants to combine them into {"a";"b";"c";"}

It's not super slick but I have doe this by creating a new column that stacks the ranges on top of each other and then reference this column in a new range. So in another column (let's say AE) rows 5-100 would equal $W$5:$W$100, row 101-175 would equal $AA$5:$AA$100, and row 176-250 would contain $AE$5:$AE$100.

To then get rid of any blank values in column AF have a counter that increments each row by one each time a cell contains a value. so if AE5 has a value it would be 1, if AE6 has a vlaue it would be 2, if AE7 does not have a value it would still be two and so on. Then in AG5 you can enter this formula: =index($AE$5:$AE$250,match(row()-row($AG$4),$AF$5:$AF$250,0))

Drag this down to row 250 and you should now see all of the none-blank value on top and a bunch of errors on the bottom. You can then create a named range on this column that has this formula: =OFFSET(Controls!$AG$5,0,0,COUNTA(Controls!$AG$5:$AG$250)-countif(Controls!$AG$5:$AG$250,"<>#N/A"))

This should now give you a final list that is a combination of the three original lists. I wish there was a way to do it without so much workout in the cells but that is the bet that I have found.

- Kevin
My Excel Page

[John Bates]

Thanks Kevin!!!! It works perfectly...exactly what I needed!!