+ Reply to Thread
Results 1 to 3 of 3

Pivot Table that just always mirrors data source?

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Pivot Table that just always mirrors data source?

    Hi!

    I have a file that looks like this:
    • Data sheet with ~ 10k rows. The columns are things like month, model, various dates, cost, etc, about 60 columns.
    • A sheet that has various pivot tables on it
    • Various sheets that have slicers that are connected, so when you select something like month, all pivot tables and slicers are filtered by just that month, etc.

    Then, there is a macro embedded (that runs whenever the data is filtered via a slicer) in the data tab that basically just filters the data tab (one column on the data tab is just true false) based on whatever slicer is selected. This is just designed so that if you select a certain model for instance, the data tab will automatically filter to show only that model.

    But it takes a good 8 seconds each time you select something via the slicer because it has to filter the data tab.

    But my question is: Is there a way to just make a pivot table that looks just like the data tab (10k rows, 60 columns)? So that if you filtered by a slicer it would just update the pivot table, just like how the pre existing pivot tables update? Or any other similar method?

    Any ideas?

    Thanks!

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

    Re: Pivot Table that just always mirrors data source?

    If I understand correctly then the attached file does what you are asking, on a small scale of course.
    The mirror PT sheet contains the pivot table that has all of the data found on sheet 1.
    The Cost by Month sheet contains the slicers which are linked to all three pivot tables.
    Note that the mirror PT is set in tabular form and that all sub totals and grand totals are removed. Also not that in the field setting for month > layout & print > repeat item labels is checked (likewise for model).
    If this isn't representative of your issue then I would suggest uploading a small sample along with specifics as to how you would like things to work.
    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
    11-08-2018
    Location
    Miami
    MS-Off Ver
    10
    Posts
    1

    Re: Pivot Table that just always mirrors data source?

    Good example. Thank you

+ 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. [SOLVED] Pivot table - Unable to navigate to source data from pivot table
    By Richavlaues in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2016, 10:37 AM
  2. Replies: 0
    Last Post: 01-06-2016, 07:00 AM
  3. Replies: 0
    Last Post: 08-14-2013, 06:31 AM
  4. Replies: 3
    Last Post: 02-27-2012, 08:03 PM
  5. Pivot table - change data source to another pivot table in 2010
    By thesecretsanta in forum Excel General
    Replies: 4
    Last Post: 04-13-2011, 12:54 PM
  6. Replies: 1
    Last Post: 06-22-2010, 09:10 AM
  7. Replies: 1
    Last Post: 06-20-2010, 04:00 AM

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