+ Reply to Thread
Results 1 to 9 of 9

Dynamic Pivot Table and Chart Cell References with slicers - HELP

  1. #1
    Registered User
    Join Date
    02-21-2018
    Location
    Georgia, United States
    MS-Off Ver
    2013
    Posts
    5

    Dynamic Pivot Table and Chart Cell References with slicers - HELP

    Hey All,

    I am working on a combination pivot table and static chart that can be manipulated with slicers. Unfortunately, applying the slicers hides my data and thus causes my chart cell references to be incorrect. I'm looking for a way to make my data selection fields dynamic so that they will properly adjust based on the slicer selections. See below for link to a screen shot of the issue

    imgur.com/a/dsLWT

    Any help would be much appreciated! I am happy to share the file if that would help.

    - Wes

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    It would be better if we had an actual workbook to work with.

    Your problem sounds very much like one I face where I want to plot something that is calculated as percent of column. I solved it using dynamic ranges. Instead of a slicer, I have a simple dropdown list that is also dynamically generated.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-21-2018
    Location
    Georgia, United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    Hello dflak, thank you for the response! Attached is the excel file for your review. Please let me know if you have questions.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    No attachment - it's easy to miss a step uploading.

  5. #5
    Registered User
    Join Date
    02-21-2018
    Location
    Georgia, United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    Unfortunately i continue to receive an error when attempting to upload the file. Here is a link instead. Thanks! ge.tt/3qvZtfo2

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    The paper clip icon doesn't work. You have to go through the steps to Go Advance, Manage Attachments, find the file, upload the file then close the upload tab.

    I can't read the link you posted.

    One of the reasons you might not be able to upload the file is if the file is too big. Cut back on the data (we only need enough to get the idea). You can also save it as a binary (*.xlsb) and if none of that works, compress it and attach the zip file.

  7. #7
    Registered User
    Join Date
    02-21-2018
    Location
    Georgia, United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    I think the file was successfully attached this time.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    I did a couple of things.

    There was something wrong with the your source data table. it wasonly "showing" two months. I rebuilt it. I attached the pivot table to the excel data table instead of the range. Now the pivot table will reference the correct data regardless of how many rows there are in the table.

    I also added a helper column that concatenates life cycle stage and channel.

    Then I added in a whole new helper sheet with a pivot table similar to the one you have displayed. This pivot table is linked to the same set of slicers that control the main table.

    Rows 1 and 2 find the column in which an item to plot is found. If, as a result of filtering, an item disappears, it points to column 13 which is blank.

    I use the column numbers to define a number of named dynamic ranges such as Plot_Active_BVM. I substituted the named ranges for your fixed ranges.

    These two wikis explain the concept behind the dynamic charting.

    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

  9. #9
    Registered User
    Join Date
    02-21-2018
    Location
    Georgia, United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Dynamic Pivot Table and Chart Cell References with slicers - HELP

    Holy Crap. This is perfect! Thank you so much!!! it's going to take me a minute to understand everything you did so that i can replicate it down the road. I greatly appreciate the effort and help on this. It would have taken me so many more hours of research to figure this out on my own.

+ 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. Dynamic Chart: comparing 2 parameters by choosing different slicers
    By survivor48259 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-11-2017, 01:43 PM
  2. Selecting Slicers in a Pivot Chart
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2015, 03:50 AM
  3. [SOLVED] Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2015, 02:27 PM
  4. slicers in pivot chart
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 06-24-2015, 02:24 PM
  5. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  6. Dynamic Chart Ranges without Pivot Table
    By edwoody in forum Excel General
    Replies: 1
    Last Post: 04-07-2011, 03:58 AM
  7. Dynamic Pivot Chart based on Pivot Table.
    By excelkeechak in forum Excel General
    Replies: 3
    Last Post: 12-01-2009, 09:23 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