Explanation

To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. When dates are grouped, they can be queried based on the numeric equivalent:

  1. Grouped by month - use numbers 1-12
  2. Grouped by quarter - use numbers 1-4
  3. Grouped by year - use year numbers (2012, 2013, etc.)

In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second:

=GETPIVOTDATA("Sales",$B$4)

This will give us the grand total. The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell.

To get the subtotal for March, we need extend the formula with the field/item pair “Date” and 3:

=GETPIVOTDATA("Sales",$B$4,"date",3)

More specific subtotal

To get a more specific subtotal for the “Hazelnut” product in March, we add another field/item pair

=GETPIVOTDATA("Sales",$B$4,"date",3,"product","hazelnut")

Which returns $5,500 in the example shown above.

Note: GETPIVOTDATA will return a value field based on current “summarize by” settings (sum, count, average, etc.). This field must be visible in the pivot table.

Explanation

Working from the inside out, we use MATCH to locate the relative position of the last entry in column C:

MATCH(9.99E+307,C5:C100)

Basically, we are giving MATCH a “big number” it will never find in approximate match mode. In this mode, MATCH will “step back” to the last numeric value.

Note: this works in this case because all values in C are numeric, and there are no blanks. For other situations (text values, etc.), see other “last row” formulas mentioned below. You will need to adjust the MATCH part of the formula to suit your needs.

Next, we use INDEX to get the address of the “entry after the last entry” like this:

INDEX(C5:C100,6))

For the array, we give INDEX C5:C100, which represents the range we care about. For row number, we give INDEX the result returned by MATCH + 1. In this example, this simplifies to:

INDEX(C5:C100,6)

This appears to return the value at C10, but in fact, INDEX returns an address ($C$10), which we extract with the CELL function and concatenate to the “#” character:

=HYPERLINK("#"&CELL($C$10)

In the end, this is what goes into the HYPERLINK function:

=HYPERLINK("#$C$10","First blank")

The HYPERLINK function then constructs a clickable link to cell C10 on the same sheet, with “First blank” as the link text.