Dynamic 2D Array

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
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
 
=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.
 
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)
 
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)
 
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.
 
Back
Top