• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Define dynamic range using Index

DE_Tx

Member
I have read several posts/threads on Chandoo.org and throughout the internet regarding this subject and most of the examples define a single column or a single row. I am attempting to define a dynamic range (rows x columns) to use in another formula. I can do it with Offset, but wanted to use a non-volatile function.
Obviously, I am not quite grasping the Index concept and would appreciate some clarification.

I have attached a sample file in the hopes that this simple example will be able to help others.
In this file, I am attempting to define the area "Rates".
In use, this table will actually be a pivot table that will grow in rows and columns throughout a year.
 

Attachments

  • dynamic range using index.xlsx
    14.3 KB · Views: 27
In your example you say "assume this is a pivot table". If so, why not use GETPIVOTDATA? This exactly does what you are looking for without any fancy dynamic reference formulas...
 
Hi,

I agree with Hui, but to answer your question about the dynamic naming of the range:

=pRates!$E$7:INDEX(pRates!$1:$6000,COUNT(pRates!$E:$E)+5,COUNT(pRates!$7:$7)+4)

please note that there is a hard-coded range as a starting point

regards

kanti
 
... I can do it with Offset, but wanted to use a non-volatile function.

Also keep in mind that any time you use INDEX in a reference with : operator, it becomes quasi-volatile, meaning the reference gets re-calculated on file open, save etc.
 
@Sajan
Hi!
That's the next from Chandoo, didn't he tell you about it? First was VLOOKUP, then follows INDEX/MATCH. :p
Regards!
 
Thanks to you all. I apologize for not responding sooner but I have been incommunicado.

@Hui - thank you for the link. That is where I want to end up. What I am trying to accomplish is to dynamically define FruitTable (using your example).

@r1c1 - I am not a big fan of GETDATA as it is nonrelative and when you copy/paste you have to change the reference for the new column (based on my experience). I want to be able to build this beast once and seldom have to modify a formula. I just want to be able to update with new data and click one button and refresh all reports (via VBA or pivot tables). I realize that Index/Match in this scenario would be semi-volatile and I can live with it.

@Sajan - I am a student of Daniel's and a member of ExcelHero forums as well and that is where I first was introduced to Index/Match. In fact, I need to go back and review some of examples that others created during our training. And, I agree with SirJB7!

@kchiba - thank you. I'll start from there and see what I can get to work.
 
Back
Top