Dynamic Range Using INDEX instead of Offset
 

Need Help to learn this:

Dynamic Range Using Index().

I can do this:
=offset(A1,0,0,CountA(A:A),CountA(1:1))

But I am trying to do it using Index Function instead of Offset.


Here are the data.

---A---------B
1--x1-------xx
2--x2-------xx
3--x3-------xx
4--x4-------xx
5--x5-------xx
6--x6-------xx
7--x7-------xx
8--x8-------xx

This can be done:
Dynamic Range Row: x1 to x8
=$A$1:INDEX($A:$A,COUNTA($A:$A))
RESTRICTED TO COLUMN A.


I have tried filling the Index Syntax: but it doesn't work.
Array-------A:A
Row_No------CountA(A:A)
Column_No---CountA(1:1)


Summary:
A Dynamic Range using Index (reads Rows & Columns).

With this, I can Incorporate this into a Vlookup(C1,[Here!!!],2,false) as an
example.

If it is not possible, just give me a holler!!!
Thanks.

Dynamic Range Using INDEX instead of Offset
 

You can incorporate the OFFSET into the VLOOKUP. Is this the reason you are
trying to find a workaround?
--
HTH,
Barb Reinhardt



" wrote:

Need Help to learn this:

Dynamic Range Using Index().

I can do this:
=offset(A1,0,0,CountA(A:A),CountA(1:1))

But I am trying to do it using Index Function instead of Offset.


Here are the data.

---A---------B
1--x1-------xx
2--x2-------xx
3--x3-------xx
4--x4-------xx
5--x5-------xx
6--x6-------xx
7--x7-------xx
8--x8-------xx

This can be done:
Dynamic Range Row: x1 to x8
=$A$1:INDEX($A:$A,COUNTA($A:$A))
RESTRICTED TO COLUMN A.


I have tried filling the Index Syntax: but it doesn't work.
Array-------A:A
Row_No------CountA(A:A)
Column_No---CountA(1:1)


Summary:
A Dynamic Range using Index (reads Rows & Columns).

With this, I can Incorporate this into a Vlookup(C1,[Here!!!],2,false) as an
example.

If it is not possible, just give me a holler!!!
Thanks.

Dynamic Range Using INDEX instead of Offset
 

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.

Dynamic Range Using INDEX instead of Offset
 

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.

Dynamic Range Using INDEX instead of Offset
 

Dear Jon,
You read my mind about the volatile part. And John, thank you. The formula
works and helpful.