Search code examples
excelfunctionexcel-formulapivot-tableformula

How to get dynamically the last value from a pivot table in Excel

I have a pivot table like the one in the image:

pivot table

The table updates every month. The question is: how can I dynamically get the last value of the table with a function, without VBA? For example, in this case the last value is -32, just above the "Grand Total" value. I'm considering to use the function "GETPIVOTDATA" but I don't know how to change the dynamic reference to the last value inserted. At the moment, the formula looks like this:

=GETPIVOTDATA("[Measures].[Count of DESIGNER]";$A$3;
"[Components_Drawings_Data].[DATE (Month)]";
"[Components_Drawings_Data].[DATE (Month)].&[giu]")
Solution

  • Screenshot (below) / here refer.

    Assuming:

    • You can isolate the pivot table so that there are no populated cells beneath it (reasonable premise, given it's 'dynamic' and so variable re: # rows)
    • You're not fixated upon using a PivotTable reference in formula
    • You do not anticipate any row labels = 'Grand Total' (which would be unusual to say the least, however see alternative function below)
    • Grand total for columns is displaying

    Then the following will achieve what you have indicated:

    ...how can I dynamically get the last value of the table with a function, without VBA?

    Screenshot for reference

    =OFFSET(INDEX($F$4:$F$100,MATCH("grand total", LOWER($F$5:$F$101),0)),0,1,1,1)

    Note: the formula in cell L3 in above screenshot includes a wrapper for presentation / ease of use only - i.e. 'Item' (col J) refers to the number of cells 'up' from the Grand Total at the very bottom of the pivot table... e.g. for item = 1 (one level up), 119 is returned (corresponding to 'woman'); for item = 2 (two levels up, 77 displays and so forth.

    The corresponding label can be determined in a very similar fashion:

    INDEX($F$4:$F$100,MATCH("grand total", LOWER($F$5:$F$101),0)-J3+1)

    Alternative/direct (does not rely upon Grand Total for cols):

    =OFFSET(F3,COUNTA(F3:F100)-2,1,1,1)