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

=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

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/

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

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