+ Reply to Thread
Results 1 to 3 of 3

Growing Pivot Table - Outside Cell References Constant Manual Adjustment

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2016
    Posts
    17

    Cool Growing Pivot Table - Outside Cell References Constant Manual Adjustment

    Hi,

    I have a pivot table that grows each month and with this pivot table I have a bunch of cell links/cell references. These references require readjustment every month as the pivot table grows. I was wondering if there is a better way to go about creating this? Is there a way to dynamically refer to the same cells with the new dates?

    In the attached example file, I have provided a snapshot of what I do for one month. So, for Sept 2019 and Sept 2020 comparison - this would be fine, my cell refences work great.

    However, when I am to update and compare just October 2019 and October 2020, all my cell references would be thrown off (because I have a new data, expanding the pivot table horizontally).

    When I load in November data, my October references will be thrown off etc etc.

    How would I go about modifying and improving upon this?

    Thanks for all your help!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Growing Pivot Table - Outside Cell References Constant Manual Adjustment

    If I understand correctly then the issue is with the formulas in M22 and down.
    For M22 try: =GETPIVOTDATA("Sum of Total Accounts",$A$17,"Date",A21,"Category","Publisher")-GETPIVOTDATA("Sum of Total Accounts",$A$17,"Date",A21,"Category","Publisher","Company Name","Sega")
    Note that the "Date" is in cell A21
    Replace the current A1 references (i.e. J22-I22) with GETPIVOTDATA functions and inside the GETPIVOTDATA function replace the DATE function (i.e. DATE(2019,9,1)) with the cell reference (i.e. A21).
    In the attached copy of the file the formulas in cells M22 and M26 have been replaced.
    Let us know if you have any questions.
    Attached Files Attached Files
    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
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2016
    Posts
    17

    Arrow Re: Growing Pivot Table - Outside Cell References Constant Manual Adjustment

    Thanks Jete, that makes sense!

    I'm not sure if you can help further.

    If I was to flip the axis, and have dates on Columns and Categories/Company/SubPortfolio on rows - how do I create a formula using your GetPivotData that I can simply drag down (Columns I29:I31) and which accommodates new information found in the pivot table?

    In this case, in October, there was a new Subportfolio for an existing Publisher. Also, in October, there's a new Publisher entirely that wasn't present on earlier archives.

    Specifically, the yellow highlights in the Attached 'Question 2' sheet.

    Thanks!!
    Attached Files Attached Files

+ 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. Replies: 2
    Last Post: 06-15-2014, 07:40 AM
  2. Pivot table that references a Cell in another sheet
    By Awilson978 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2012, 12:53 AM
  3. controlling pivot table with cell references
    By tinkerbelle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2010, 05:07 AM
  4. Macro Pivot Table vs Manual Pivot Table - different results
    By vodka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2008, 04:43 PM
  5. Pivot Table and Cell References??
    By gillyd in forum Excel General
    Replies: 0
    Last Post: 10-02-2006, 03:56 AM
  6. Keeping cell references constant
    By FlyingDutchmanIam in forum Excel General
    Replies: 1
    Last Post: 06-29-2006, 06:50 AM
  7. Replies: 4
    Last Post: 04-16-2005, 12:06 PM

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