+ Reply to Thread
Results 1 to 6 of 6

Dynamic row referencing with changing source data in Pivot Table

  1. #1
    Registered User
    Join Date
    08-16-2022
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    5

    Question 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.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic row referencing with changing source data in Pivot Table

    I am not sure that the 2016 version of Excel supports this however I'll throw it out there.
    1. Build the trend pivot tables with Last Name in the Rows area
    2. Pull the High Score column into the Values area twice
    3. For the first field of the high scores > from the field values settings select to show values as Rank Largest to Smallest with the base field being the player last name.
    4. Choose top 3
    5. Choose more sort options > Descending by High Score (the second field)
    6. Hide the Last Name column
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-16-2022
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic row referencing with changing source data in Pivot Table

    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!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic row referencing with changing source data in Pivot Table

    Perhaps the following:
    1. Add a 'Rank' column to the source data using: =SUMPRODUCT((C$2:C$21=C2)*(D$2:D$21>D2))+1
    2. Change the source of the pivot tables to include the 'Rank' column
    3. Pull the Rank field into the Rows area above Player last name
    4. Change the report layout to tabular
    5. [Optional] Select do not show subtotals
    6. Use the following GETPIVOTDATA formula: =GETPIVOTDATA("High Score",Pivot!$M$4,"Rank",A9)
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-16-2022
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic row referencing with changing source data in Pivot Table

    JeteMC,


    I was able to get my final data summary tables of the top 3 scores in 'Trend' to dynamically reference the pivot tables in summary using the rank number as the reference/primary key using the VLOOKUP() function instead of GETPIVOTDATA.

    Thanks for your assistance on this matter.
    Last edited by SeaPh03nix; 10-24-2022 at 11:05 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic row referencing with changing source data in Pivot Table

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing data source in pivot table
    By viber52 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2015, 09:14 AM
  2. [SOLVED] Changing source data on a Pivot table by VBA
    By ZuneidDassu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2014, 04:23 AM
  3. Changing data source to update pivot table
    By jonesli in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-07-2014, 01:48 AM
  4. The correct way of referencing the data source for a pivot table... error
    By lgjmac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2013, 03:51 PM
  5. Changing Data source of a pivot table
    By ram-gopal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2012, 01:28 AM
  6. Changing the source data in a pivot table... how?
    By shadestreet in forum Excel General
    Replies: 1
    Last Post: 04-27-2007, 11:43 AM
  7. Changing Data Source for Pivot Table
    By jjj in forum Excel General
    Replies: 1
    Last Post: 04-13-2006, 04:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1