Customising GETPIVOTDATA formulas

A typical GETPIVOTDATA formula in Excel might look like this:

=GETPIVOTDATA("Sum of ValueField",$A$3,"field1","a","field2",1)

Of course, all of these references are fixed so not much use if you want to copy the formula using different values.

Linking the criteria (the “a” and 1 in the original formula) to input cells is straightforward – let’s assume those values have been entered into J1 and J2 respectively; we can then adjust the formula to:

=GETPIVOTDATA("Sum of ValueField",$A$3,"field1",J1,"field2",J2)

Similarly the field names to which those criteria apply can also be made into cell references – let’s assume K1 and K2 contain field1 and field2 respectively:

=GETPIVOTDATA("Sum of ValueField",$A$3,K1,J1,K2,J2)

Simple – and makes it easy create summary tables of sections of the pivot, or to pick out different values using input criteria.

Now you would think that you could do the same with the “Sum of ValueField” but you will find that if you enter that text into cell L1 and try:

=GETPIVOTDATA(L1,$A$3,K1,J1,K2,J2)

you end up with a #REF! error. The solution is simple but not really obvious – you need to prepend (or append) quote marks:

=GETPIVOTDATA(""&L1,$A$3,K1,J1,K2,J2)

 

2 thoughts on “Customising GETPIVOTDATA formulas

  1. Pingback: Pivot Table - Sort by Year Without Grouping

  2. Pingback: Getpivotdata error

Leave a Reply

Your email address will not be published. Required fields are marked *