Dynamic 2D Array

E

ExcelMonkey

I know that a Dynamic 1D array for data in column B starting in B2 is:

Offset($B$2,0,0,CountA($B$2:$B$635586),1)

But what if I want to make this a a 2D array
 
K

Ken Wright

=offset(reference,rows,cols,height,width)

You currently have the height dynamic and the width set to 1 - Just change the
1. A positive 2 will give you the current column and the one to the right, a 3
will give you current and 2 to the right. A negative 3 will give you current
and 2 to the left.
 
R

Rob van Gelder

ExcelMonkey,

I've done very little with dynamic arrays. I imagine the formula would be:
Offset($B$2,0,0,Max(CountA($B$2:$B$65536),CountA($C$2:$C$65536)),2)
 
E

ExcelMonkey

Sorry I posted to the wrong group. But I answered my own question.

=OFFSET('$B$2,0,0,COUNTA('B$2:$B$65536),COUNTA('$B$2:$IV$2)
 
K

Ken Wright

Note that this ONLY works if you have NO blanks in that range, else the COUNTA
will return the wrong value for what you are trying to do.
 
Top