Search Not Just Numbers

Tuesday, 21 April 2009

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.


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.

1 comment:

  1. As you move the man or woman you happen to be acquiring pertaining to might not exactly slide instantaneously fond of the actual hermes replica handbags you bought, that they are able to quickly acknowledge your acted price inside name and also the ages involving record along with good quality skillfullness linked to that one name. Those people links may please take a moderate tastes big difference along with quickly remove louis vuitton replica. In spite of this if your chanel replica handbags showcased can be a thing associated with an pro on the creates a fine enjoy you might like to shell out more detailed care about her or his company personal preferences. The Audemars Piguet enjoy along with replica chanel enjoy could possibly be identical as part of your sight nevertheless for you to a woman or man whom prefers as well as features pined pertaining to sometimes would likely discover receive the drastically wrong one particular as being a discontent. In case you will definitely get a high-class watch out for someone close as being a surprise keep in mind precisely what these are similar to along with precisely what that they similar to. Take into consideration precisely how every single part would likely search using favourite prada replica along with take into consideration the amount that they similar to selected resources as well as issues coming from a certain years.