+ Reply to Thread
Results 1 to 20 of 20

Referencing pivot tables against cell value itself in a pivot table operated by slicer.

  1. #1
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Question Referencing pivot tables against cell value itself in a pivot table operated by slicer.

    Hi all,

    I'm trying to get several pivot tables referencing a cell that itself is within a pivot table currently operated by a slicer and the reference would remain the same in this case $O$5. Unfortunately my data if from different sources so I can't link my pivot tables to a single slicer to perform the function. Ideally I would like to retain use of the slicer function to allow me to switch between product codes easily. My current slicer is working from a main table of complete product codes and the remaining ones are shorter in length and may omit product codes as there is no data against them.

    Ideally I would like to be able to get the whole sheet to reference the pivot table the slicer is attached to immediately filter the other pivot tables on the same value. I've done some research and it seems that it could be done with VB saved in the sheets view code on the sheet tab. I've found some cases of people trying to reach something similar but nothing quite as specific as to what I'm after. I would like to retain the use of the slicer ideally. Also any ideas on what would happen to a code that's within the main table but not in the other tables and if blanks would be selected for the other pivots would be interesting to hear as it's part of a dashboard I'm putting together so if other codes remain in the pivots essentially not in use could be misleading.

    Any help or advice would be much appreciated.

    Kind Regards,
    Haydn

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Are you trying to set the filter on multiple pivot tables to the same value simultaneously? Also will you be selecting only one item, or do you wish to select multiple items? Finally are all the selections available for all pivot tables?

    If it is a single selection, then the process should be fairly easy. Use the slicer to set the filter on one table, then use a Change event on that filter's cell and simply copy the target value to the other filter cells. If you select multiple values with the slicer, then it becomes more complicated, but still doable.
    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
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Hi Dflak,

    Thank you for your help, I'll answer you as best I can.

    Are you trying to set the filter on multiple pivot tables to the same value simultaneously?
    Yes this is what I'm trying to do.

    Also will you be selecting only one item?
    Yes it will only be one item at a time.

    Finally are all the selections available for all pivot tables?
    Unfortunately no, in a couple of pivot tables there will be no record against a product code that I'm filtering on as there are no stock movements/sales data.

    Would you be able to describe the process of adding the change event on a filter? How do I go about this I presume this is VB? I have very little knowledge in this area. When you way you copy the target value to other filter cells is this done in view code or is it done entirely differently?

    Your post gives me hope and thank you for the help!

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    If you could provide a sample workbook, it would help a lot. Otherwise it would take me time to develop data to test it on.

  5. #5
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Oh thank you that'd be a great help. I'll get one put together for you tomorrow as currently there's lots of linked data to a live sheet and I'll have to paste values over them so it'll work.

    Thanks again for you help.

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Generating sample data can be a bear, but it would be better if I had things exactly in your format so any solutions I come up with won't have to be adapted that much .

  7. #7
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Hi Dflak,

    I've uploaded a condensed workbook. It's basically the pivot table on "Cart Dash" that I'd like to link from one slicer referencing a cell in this case L11. If it is possible I'll then be doing the same for the remaining two dashboards. Ideally I'd like to get each pivot table working from one slicer. Anyway there's absolutely no rush and I'm appreciative of your time and offer to help.

    Many thanks,
    Haydn

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    I get the idea that you want to synchronize the filters on the * Dash tabs. Can you identify for me which cells contain the filters (I can take a guess, but I'd rather be sure) and which are the "master" (the ones linked to the slicers) and which are the subordinate cells (the ones you want to sync with the master.)

    I sort of mapped out how to do this, but it's going to take some adaptation.

  9. #9
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    OK so the filters on the pivots I want to filter on are in D5, D32 and L10. Currently the "Cartridge Orders" slicer refers to the pivot table in D5 and the "Stock Forecast" slicer is linked to L10 & D32. Is this the information you mean? Oh I should mention as well that I'm only after filtering on one "Dash" sheet at a time not all three!

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    This has been an interesting project.

    There are a couple of changes I’ve made to how you reference the source data. I did this so you reference only the exact amount of source data you need, and don’t have (blank) or #N/A (except where #N/A exists within the body of data). In other words, I’m trying to keep you from having to redefine the source data for the pivot tables every month.

    The two methods I used were Excel Tables and named dynamic ranges.

    Here are a couple of articles that explain both of these concepts.
    http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
    http://www.utteraccess.com/wiki/inde...ables_in_Excel

    For “open ended” data such as Cart Pivot, Noz Pivot and Pis Pivot, tables work very well. When you get a new set of data, remove the data by selecting the data rows (not the headers), right clicking and selecting Delete -> Table Rows. If you see Delete -> Entire Row, it means you have hidden cells. Unhide them and try again. Then copy and paste your new data in.

    One of the advantages of tables is that tables know how many rows they have. There is no guessing as to how many rows to include as a pivot table data source.

    If you want, I can give you a macro tied to a button that will remove the data from these three tables: it’s “off my shelf” code. It will take about 5 minutes to code it.

    The other data sources are the production plans. The data you want from these is embedded with other data so I have to parse it out with a named dynamic range.

    For example the name Cart_Pivot_Data is defined as =OFFSET('Production Plan Cart'!$U$6,0,0,COUNTA('Production Plan Cart'!$A:$A)+1,25)

    We can take this formula one piece at a time.
    'Production Plan Cart'!$U$6 – start in Cell U6
    0,0 – go down zero rows and right zero columns (so we are still in Cell U6)
    COUNTA('Production Plan Cart'!$A:$A)+1,25 – from that cell, reference a range COUNTA('Production Plan Cart'!$A:$A)+1 rows deep and 25 columns wide.

    It looks like the shape of your data is changing. You seem to be adding one column per month. Tell me more about how you add data to this page, and I will come up with a more flexible formula for nailing down the range. It may depend on finding the column in which “Corresponding Code” is found and a count of column headers in row 6. I’ll probably use helper cells on the Parameters page.

    I made the tables and the named ranges the new data sources for the pivot tables. I had to delete the Stock Forecast slicer to be able to switch the data source. If you still need it, you can add it back in.

    The named ranges are outlined on the Named Ranges sheet. This sheet is for verification purposes only. It’s not needed to run the report. XXX_Pivot_Data are the data sources for the pivot tables. XXX_Col_U is the list of Corresponding Codes. I also have some static ranges I defined so I know what I am looking at in the code.

    I also took the liberty of naming the pivot tables to names like PT_Cart_D6, PT_Cart_L11, etc. so I can work with them in the code. These names make more sense than “Pivot Table 1” etc.

    I stuck with the D6, L11 and D33 naming conventions even though these cells vary from page to page.

    Parameters Sheet: I use this to put my helper cells and intermediate calculations. Column B is the current value of the master filter on the appropriate page. Column C is the count of how many times this value appears in the source data.

    This is important, because I do not want to attempt to set a filter in a subordinate table if it is not a valid value in the subordinate table. I do a COUNTIF against Column U and if it’s zero, then I don’t want to set that filter.

    I defined a lot more than I really needed here.

    I will go through the code for the Cart Dash page. Each of the dash pages has similar logic. The process is kicked off by a Change Event. When you select a slicer, it changes the value on the master filter.
    Please Login or Register  to view this content.
    If the filter is processed then it passes the appropriate parameters to the SyncFilter macro.
    Please Login or Register  to view this content.
    I know this is a lot to absorb, but if you have any questions feel free to ask.

  11. #11
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Wow Dflak,

    Can I just say thank you for the time and effort you've firstly put into this and the detailed explanation. I've pm'd you directly. As you've said there's a lot to absorb and I'm going to have a good read of the links you have included and explanation of everything. I'm sure I'll have questions but I'm going to sleep on it now and come back with fresh eyes tomorrow and attempt to understand what's happening here. I'll report back!

    Haydn

  12. #12
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Hi Dflak,

    I've attempted replicating everything you've described and had a good read - it's been a great exercise so far for me. I should also know better regarding the tables although the named dynamic range is a completely new concept to me and a head scratcher initially with the -1 to exclude headers. Within the production plan the data isn't being added to but rather once a week is finished the following weeks orders are brought froward so weeks 2-6 effectively are copied into the commencing week (and dates changed) where orders are then moved about again and may be added to week six if that makes sense. The forecast is then used to highlight any shortages on materials on the dashboard to work out accumulating totals by week against current stock levels and it can be identified what weeks things need to be ordered if there are shortages.

    I'm pleased to say I managed to replicate everything in my full work book and it's currently working as the one you attached.

    My goal however is to use a slicer to switch the data that's applicable to all the codes which appear in the product plan, and ideally if at all possible not show any data in the pivots where there's no data against that code. For example the production plan essentially shows all the codes available on the system, however there may not be any orders against this code nor may there be any stock against the code - I would however still like to see the code data that is possibly available ie all codes and any requirements, current stock levels and finally if there are any orders. It's my understanding that it's currently working where the criteria is met where there a) it appears in the production plan (all codes will meet this requirement) but b) there's a count against the code in the pivot table data (these tables are works orders against the code that have previously been placed to bring stock in on time).

    I tried adding another slicer against L6 on the cart dash which listed all codes however it just changes that respective pivot? Even when I selected a code where criteria are met it didn't change them.

    I hope the above makes sense - my brain is now frazzled by the end of replicating the steps and attempting to understand it all! But I can see that the VB and the steps required work exactly as you interpreted. I should have done better explaining initially what I was after!

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Some more questions, of course .

    Are you telling me that the area for the production plan columns is fixed? If so, then the named range definitions are good. if you will be adding or deleting columns, then the definitions will need to be changed to be more flexible.

    If I am reading the last post properly, you want the slicer to be driven from the production plan and not from the XXX Pivot sheets. Is this correct? At the moment, I have it the other way around. The same logic will apply, I'll just have to switch what's pointing to where. Fortunately, I have the part of the code that does the heavy lifting isolated and set up to accept parameters, so this is a relatively easy task.

    So what I will have to do is set up the slicer to look at Cell L11, change the target on the change event to look at Cell L11 and update the other two tables.

    I won't do anything until you confirm or clarify.

    Expressing requirements is a difficult process. I know. I have to do it for a living .

  14. #14
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Yes the area for the production plan columns is fixed. Columns won't be added but rows may occasionally may be removed or added albeit fairly infrequently as these codes come and go.

    Yes that's right I'd like the slicer to be driven from the production plan so all codes are available to be clicked.

    It's a good thing I don't express requirements for a living!

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Requirements definition is an art. I have a checklist I started back in 2006 and every time we have a design review I add some items to it. I am sure the developers don't go out of the way to misunderstand the requirements, but if they can be misunderstood, they will be. It's all part of Murphy's Law.

    I should have time to work on this today. Good news about the fixed columns. I've already made provision for counting the rows.

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    OK, I think I have it this time.

    In each of the slicers, there is a (blank) entry. This is because there is a blank row between the header row (Row 6) and where the data starts (Row 8). I can think of a way to get rid of it, but I just can't think of an easy way to get rid of it. Likewise for some of the #N/A for product in one of the data sources. Essentially, I'd have to copy the data to another place and remove these rows. I might experiment with some array formulas. I'll let you know if that works out.

  17. #17
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Well it sounds like you've got extracting the required information you need down to an art form before you design and go ahead and consider the next stages. I'm sure there's been several occasions where you've been very thankful for this good planning and foresight. Although Murphy's Law will remain exactly that just to keep us on our toes.

    Thank you so much for the returned sheet. It's working exactly as I require. Admittedly after a busy few days I haven't implemented it into the main plan yet but that's the next step for me and to grasp the concept and roll it out to a few others. It's the little details and selecting one slicer is going to be far more convenient. I can assure you the blank entry is not even an issue, just see the perfection in imperfection! Thank you for your patience and superb explanations, I've enjoyed learning some new concepts and will try and keep up good practice.

    I'll see if I can think of a few more head scratchers in due course!

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Glad to be of service!

  19. #19
    Registered User
    Join Date
    11-01-2010
    Location
    London, england
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    Hi Dflak,

    Me again! I tried to implement these changes into my main spreadsheet last Friday however I had some issues with the code. I believe I set everything up correctly and had my tables, named ranges and countif, viewcode VBA & ModSyncFilters sheet all exactly as they should have been or at least reflecting what I could see in the last workbook you sent over.

    Basically when I selected a code on the slicer I had a "Compile error: Sub or Function not defined" when I selected ok to this the top line was highlighted yellow on line "Private Sub Worksheet_change (ByVal Target As Range)" with an arrow next to it from the debug. I removed the slicer and re-linked the two pivot tables but still had the same issue.

    I can send the screenshots if necessary but do you have any advice as I can't tell if there's something obviously wrong?

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

    Re: Referencing pivot tables against cell value itself in a pivot table operated by slicer

    I'll start with the obvious: is the code on the sheet module instead of a regular module?

    Make sure that the "trigger" cell on the spreadsheet matches the cell in the intersect line in the code.

    Make sure the pivot tables on the spreadsheet match the names of the pivot tables in the code. The names should be:

    Cart Dash PT_Cart_D6
    Cart Dash PT_Cart_L11
    Cart Dash PT_Cart_D33
    Noz Dash PT_Noz_D6
    Noz Dash PT_Noz_L11
    Noz Dash PT_Noz_D33
    Piston Dash PT_Pis_D33
    Piston Dash PT_Pis_D6
    Piston Dash PT_Pis_L11

    With a field in the pivot table selected, got to Pivot Table Tools -> Analyze. On the top-left of the screen is the pivot table name. You can type in the new name.

    That's about all I can think of for now.

+ 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. Connecting two or more pivot tables from one slicer
    By meus in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-03-2016, 01:11 PM
  2. [SOLVED] Sumifs formula referencing cell generated by slicer/pivot table
    By 3345james in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-22-2015, 03:26 PM
  3. Is It Possible to Connect A Slicer to Two Pivot Tables?
    By NewJerichoMan in forum Excel General
    Replies: 2
    Last Post: 04-26-2015, 01:35 PM
  4. VBA to call out slicer from Pivot table based on cell value assigned.
    By jimmywong98 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-25-2014, 10:45 PM
  5. [SOLVED] Link Pivot Tables, Filter with One Slicer
    By Pierce Quality in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-23-2013, 11:19 AM
  6. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM
  7. Pivot Table (Slicer) Help
    By skate1991 in forum Excel General
    Replies: 0
    Last Post: 10-11-2012, 11:52 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