Hello helpful people,
From an empty cell (lets say cell "A1") I'm trying to reference a prevalent value in a row from a pivot table. This all works fine by entering in the cell the formula "=GETPIVOTDATA(arg1, "arg2", field2, etc.)" or just clicking on the cell with the prevalent value on the pivot table itself and letting excel reference it for me. Multiple pivot tables actually, but same scenario.
The pivot tables in question essentially show the top 3 values of all the rows, one pivot table for one category and another for a 2nd category.
The problem arises when I change the source data. The data that the pivot tables analyze & summarize is some tens of thousands of rows long, and it changes constantly every few days. So I've programmed VBA code in a macro for my spreadsheet that imports the new data whenever it updates. Fortunately the data updates don't really consist of changes to the data format, so I can just right click my pivot tables and hit refresh.
I've created a sample spreadsheet, showing how it's set up originally, and a 2nd one where I've altered the source data in a manner resembling what might look like my actual data "updating". For the samples I used the context of video game players & their high scores, for 2 different games (2 different categories).
And although the pivot tables successfully analyzes and reports the key figures of the data in the way I've configured it to do so, the cell that references the pivot table values doesn't dynamically 'refresh', because one of the "args" that the GETPIVOTDATA(arg1 field1,...) takes as parameters is actually another tuple/cell value from the same row as the original referenced value; like a primary/unique key (effectively these values are). In my example, these unique keys are last names which in reality will not always be unique but lets ignore that as it doens't contribute to my problem, my actual unique keys are unique.
This doesn't work because when the data updates, the pivot table will have (if not immediately, eventually) new selected rows/values for the top 3 prevalent values, or high-scores in my example. And along with those new prevalent values / high scores, will be 3 new primary/unique keys, or last names. so the 3 original last names / keys that =GETPIVOTDATA(...) was using to reference will not be found in the top 3 pivot tables, and the cell "A1" will display a #REF! error.
Ideally, what I need to do is have a 3rd column in these pivot tables that just has ranking numbers (like 1,2,3), so that the GETPIVOTDATA function could use these ranking numbers as the unique keys and not the 'last names'. I've superficially demonstrated what I mean by adding what such extra columns would look like in the columns next to my pivot tables in my example. Note that this extra column doesn't actually have to have the ranking right, in terms of ranking #1's highscore is > #2's highscore, which is > #3's highscore. If the rankings are wrong each time, that's actually fine, because in the end of the day the GETPIVOTDATA() function could still reference a 'highscore' value with a 'rank' value of 1,2, or 3 to retrieve. For my purposes, I've assessed that to be non-essential.
is there any way to do this with pivot tables? if not, would it be safe/effective or functional to use a =VLOOKUP() to locate the updated top 3 values, using my simple added on column? Or if neither, is there anyway to possibly do this with some VBA Macro programming?
Please see attached sample spreadsheets. Thanks for reading and for any input, it is always appreciated.