Problem with Dynamic Range Reference to Pivot Table Array

[InfrequentVisitor]

Hello,

The Background:
I'm currently working with a pivot that is drawing information from a query. The query's number of rows change, so the pivot's does, as well. The number of columns won't change anytime soon.

The pivot is located in the CBPivot tab. I'm using Index/Match to pull the information from the pivot into a cell:
=INDEX(CBPivot!$B$3:$BP$11803,MATCH($C30,CBrow,0),MATCH(F$29,CBcolumn,0))

Where CBrow & CBcolumn are offset functions to adjust the row & column references as the pivot changes size.
CBrow = OFFSET(CBPivot!$A$3,0,0,COUNTA(CBPivot!$A:$A)-1,1)
CBcolumn = OFFSET(CBPivot!$B$2,0,0,1,COUNTA(CBPivot!$2:$2)-1)

The Question:
Is there a way (preferably with formulas over VBA, but I'll use VBA, if needed) to make the array reference CBPivot!$B$3:$BP$11803 in the INDEX function dynamic, so that it changes with the pivot's size?

- Thank you

 

[Jerry Sullivan]

Have you considered using a GetPivotData formula that has relative references to your Row and Column lookup values?

One benefit of the GetPivotData function is that you only need to reference one cell on the PivotTable so you can reference the static cell CBPivot!$A$2.

 

[InfrequentVisitor]

Have you considered using a GetPivotData formula that has relative references to your Row and Column lookup values?

One benefit of the GetPivotData function is that you only need to reference one cell on the PivotTable so you can reference the static cell CBPivot!$A$2.

Mr. Sullivan,

After some trial & error with your suggestion, I can say that is the perfect fix to my problem.

Much obliged.

- Eric