You need to specify two opposite corners, not necessarily top left and
bottom right. This does bottom left and top right:

=INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)):INDEX(Sh eet1!$1:$1,COUNTA(Sheet1!$1:$1))

I don't know your objection to OFFSET for arrays, but INDEX has the
advantage of not being volatile.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


" wrote in
message ...
Dear Barb,

Thanks for responding.

It's not the reason. So far I know of 2 types of dynamic range:
1. Offset
2. Indirect + CountA - for array formulas

Now, I am looking for the Index.
=$A$1:INDEX($A:$A,COUNTA($A:$A)) <-- only reads 1 column.
But I want one that reads more than 1 column, using the Index.

I had a feeling it's not quite possible..lol.