GETPIVOTDATA is a brilliant formula that allows you to effectively run a VLOOKUP within a Pivot Table. It is possible to make the GETPIVOTDATA use dynamic references (i.e. you can make it look up information within a Pivot Table by referring to a cell which you can change), but the one that doesn’t work so well is the Data Field (the one that says something like Sum of Last Year). There is a small trick to make GETPIVOTDATA Data Field a cell reference
Estimated reading time: 3 minutes
Table of contents
YouTube GETPIVOTDATA Data Field as a cell reference
Watch, like, subscribe
To learn more about GETPIVOTDATA and its uses in Dashboards or Management Reports, have a look at our online MS Excel Dashboarding course.
Adjusting the GETPIVOTDATA formula for dynamic references
As shown below, when you create a GETPIVOTDATA formula you can refer to cells in the spreadsheet to do the lookup. So in the Item1 below instead of typing in Apr 2018, we have linked to cell C3 which has a dropdown with possible dates. This works well and makes the GETPIVOTDATA (and any reports it is in) dynamic like a Pivot Table without forcing you to be constrained with Pivot Table look and feel.
The problem is the Data_field. As shown above, if we link to cell C2, which contains exactly what we want (“Sum of NET”) it results in a #REF! error message, as if it doesn’t exist in the Pivot Table. If you type it in, it works.
Trick GETPIVOTDATA to read Data Field
There are a few ways to do this, but we will use a common function that most people should know, i.e. CONCATENATE (now CONCAT in the latest versions).
By putting a CONCATENATE around our reference to cell C2 (even though we are not joining anything together as shown below), it now ‘sees’ the text and pulls through the correct numbers. More importantly you can now set up the Data_Field to be dynamic as well, so that the user can decide if they want to see, for example, this years numbers, or last years numbers!
You can learn more about Pivot Tables in our MS Excel Pivot Tables Online Course.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
How can I fill the empty labels with the headings in a Pivot Table
Excel Dynamic Range- Pivot table includes new data as it grows