Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
Offset function problem-Dynamic range | Excel Discussion (Misc queries) | |||
Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
Offset, Dynamic range, Countif | Excel Discussion (Misc queries) | |||
dynamic range / offset | Excel Worksheet Functions |