Referencing Pivot Tables correctly using Index/Match

[ExcelAtEverything]

I have Pivot Tables which will expand & retract over time depending on the current information loaded into them. There are times when I am using Index/Match to retrieve info from the Pivot Table, as opposed to GETPIVOTDATA. What I am noticing however is that when I select the array for the Index or Match formula (& select the entire range from left side of table all the way to the right side in any given row), sometimes it selects actual cell references, and sometimes it gives me actual table references. This is confusing to me because I thought that as long as I copied the entire row, that my formulas that reference this Pivot Table would expand or retract to match the Pivot Table info (as the info increased or decreased in number of cells over time). So when I am seeing cell references it's making me think that it is going to expand when the data does. Can anyone help me understand this?

 

[jasonb75]

and sometimes it gives me actual table references

That only happens when you select a single cell in the pivot table, more than one cell selected will give you regular cell references.

Is there any reason why you can't use GETPIVOTDATA for all instead of trying to use INDEX and MATCH for some?

 

[ExcelAtEverything]

Yes. Here is the short thread explaining th answer to your question.

Help with GETPIVOTDATA formula

I feel like I am having a senior moment here, and could sure use some assistance. I cannot for the life of me get this simple GETPIVOTDATA reference to become dynamic. In the Pivot Table below, the "Primary Categories" are items like basketballs, baseballs, footballs, soccer balls, etc. As it...

www.mrexcel.com

 

[ExcelAtEverything]

What I just sent there is just a link to another thread I have going here where I am having problems using GETPIVOTDATA for this same issue.