Dynamic 2D Array
 

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

--
Message posted from http://www.ExcelForum.com

Dynamic 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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ExcelMonkey " wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Dynamic 2D Array
 

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)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ExcelMonkey " wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/

Dynamic 2D Array
 

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)

--
Message posted from http://www.ExcelForum.com

Dynamic 2D Array
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ExcelMonkey " wrote in message
...
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))


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004