Excel Tip: Using GETPIVOTDATA (part 2)

This post continues from where we left in Excel Tip: Using GETPIVOTDATA (part 1) . We had explained the construction of the GETPIVOTDATA formula and how to use it to report a particular figure from the pivot table.

In this post we will look at the flexibility that can be achieved by using formulae to populate some of the arguments in the GETPIVOTDATA function.

To recap, the GETPIVOTDATA function has the following format:

=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")

but each of the text fields (in inverted commas) can be replaced by formulae which, used intelligently, allow you to produce management accounts and reports in any format.

Examples:

You could replace "Name of data field to return" with a reference to the cell at the top of the column in your final report, which can hold the name of the data field you wish to return. By using [F4] to insert a '$' in front of the row but not the column in this reference, you can copy the formula across multiple columns using different data fields from the pivot table in each column on your final report. This is useful for reporting, say, month and year-to-date figures in management accounts.

Where the field item refers to the name of the raw data column containing a code that determines where the values go in your final report (e.g. a management accounts code) - if you replace the corresponding "item" field with a formula referring to the first column of the spreadsheet, this can be used to populate the rows of your final report. By using [F4] to insert a '$' before the column and not the row, you can now copy the same formula into every cell in your management accounts. Each row will show the value for that row, based on the code in the first column and each column will include the data field to use for the value, based on the first row.

How you get theses management accounts codes into the raw data is covered in an earlier post:

Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.