Define dynamic range using Index

[DE_Tx]

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.

 

[Hui]

Does the technique I describe at: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
help you ?

eg: =SUMPRODUCT((d)*(Months=5)*(Campaigns = D11))
where
d=pRates!$E$7:$P$78
or
d=OFFSET(pRates!$E$7,,,COUNTA(Campaigns),COUNTA(Months))

 

[r1c1]

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...

 

[kchiba]

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

 

[r1c1]

... 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,
The following article by Daniel Ferry provides a great introduction to the capabilities of the INDEX function, and specifically how to construct dynamic ranges:
http://www.excelhero.com/blog/2011/03/the-imposing-index.html

Cheers,
Sajan.

 

[SirJB7]

@Sajan
Hi!
That's the next from Chandoo, didn't he tell you about it? First was VLOOKUP, then follows INDEX/MATCH.
Regards!

 

[DE_Tx]

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.