Dynamic row referencing with changing source data in Pivot Table

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.

Thanks for your input JeteMc. Using your guidance I was able to implement the ranking into all the pivot tables needing it correctly.

There was however an obstacle I encountered after that implementation. I see in your edited version of my spreadsheet that you've added the ranked pivot tables in the sheet 'Trend Analysis', implying that pivot tables should be used instead of the non-pivot tables I have in that sheet, cells "A8:B17". The reason I don't use pivot tables here is because of a factor I omitted in my sample spreadsheet / metaphor to avoid complexity and confusion. In my real/actual data, I have an additional dimension/category of higher level/ prioritization than others. To make it applicable to the example, this dimension would be something like "console". So, of my 20 data points in the example, 10 would be under "PC" and another 10 in "Nintendo 64", with equal distribution of the subcategories within. So effectively, my table in 'Trend Anaylsis', cells "A8:B17" combines the top 3 Snake high scores in PC and the top 3 Snake high scores in Nintendo 64 to show the top 6 by rank, from which I then select the top 2 high scores and add them together in cell "C1".

Here is where I encounter a difficult issue I can't seem to resolve on my own. When I program GETPIVOTDATA() to reference the 'top 3 Snake High scores on PC', I found that excel automatically / by default looks up the value requested (High Score) in the pivot table using the first column: in the example "Player Last Name" (the variable put into the row box). I don't want it to do that because the player last name will change and the reference will screw up. So, I tried programming it to use the "Rank" number as the reference, but when I do it like this, it returns a '#REF!' error. Here's what that line of code would look like, in your edited version of my sample worksheet:

=GETPIVOTDATA("High Score ",$F$8,"Rank","1")

Unfortunately however, this returns a '#REF!' error.

Is there anyway to configure / program this to look up the 'High Score' value using another category under the 'values' box of the pivot table (High Score / renamed to "Rank") as the primary/unique/reference key instead of the category in the 'rows' box?

I've thought, perhaps this way might work:

1. In empty unused cells like "K8", use GETPIVOTDATA() to reference the player last names of the players with top high scores in either PC/Nintendo 64 by the rank numbers: (1,2,3)
2. In empty unused cells next to those, use GETPIVOTDATA to reference the High Scores of the players with top high scores in either PC/Nintendo by their last names, referencing the previous result ("K8") for the last names.
3. Create my tables in cells A8:B17 by referencing cells programmed in K8:++

However, this still requires GETPIVOTDATA to reference the pivot tables with something other than the player last names as the primary/unique/reference key (using instead their rank number).
Will try when I get a chance and report. Thanks for your input!