+ Reply to Thread
Results 1 to 6 of 6

1 slicer for 8 different data sources

  1. #1
    Registered User
    Join Date
    11-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    9

    1 slicer for 8 different data sources

    Hi there,

    I got an workbook with 8 pivot tables of 8 different data sources. All 8 data sources have 1 equal column with week numbers. Somehow I want to be able to use 1 slicer or one other button that filters the weeks that I select on this slicer/button on all 8 pivot tables. Is this possible?

    * Just simple Pivot tables connections is not possible because therefore I need 1 data source.
    * Report connections after 'Use this workbook's datamodel' is also not possible because when I try to link the week columns I get the error 'Both columns contain duplicate values'. But this I can't change because in all 8 data sources there are many rows that contain te same week number.

    Is there an other solution?

    Kind regards,

    Bert

  2. #2
    Registered User
    Join Date
    11-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    9

    Re: 1 slicer for 8 different data sources

    Anyone?

    I really could use some help.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: 1 slicer for 8 different data sources

    I'm not aware of any slicer functionality that allows that in standard Excel.

    You may find that the Add In 'Power Pivot', which allows you to join data sources in one Pivot table will handle this with a 'power slicer'

    Alternatively you'll need a macro. Upload the worbook for a more informed comment
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: 1 slicer for 8 different data sources

    yups power pivot model is the way to go..

    see how you can use a date table to use across many different sources
    https://support.office.com/en-us/art...5-c72ba796d31e


    anohter route could be to use VBA to copy the chosen week in one pivot to the other pivotsources...

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: 1 slicer for 8 different data sources

    You already tried using the data model, so you know how that works. The missing bit here is that you need a calendar table. You don't have to go full out with a table that contains all days. Maybe in your case it would be sufficient to have just a week number table. Then create relationships from all the other table to the calendar table. When you create the slicer, use the calendar table week number.

  6. #6
    Registered User
    Join Date
    11-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    9

    Re: 1 slicer for 8 different data sources

    Quote Originally Posted by teylyn View Post
    You already tried using the data model, so you know how that works. The missing bit here is that you need a calendar table. You don't have to go full out with a table that contains all days. Maybe in your case it would be sufficient to have just a week number table. Then create relationships from all the other table to the calendar table. When you create the slicer, use the calendar table week number.
    Thank you so much! This is exactly what I needed! The only disadvantage of this solution is that I can't use calculated field with my pivots. But therefore I made some columns next to the pivots with formulas instead of calculated field. Thanks a lot!

    To the others that adviced me to use Power Pivot: Thanks for the advice! I really should learn more about this. For now the solution above was the most easy one for me.

+ 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: 3
    Last Post: 09-03-2019, 10:22 PM
  2. Replies: 0
    Last Post: 08-19-2019, 07:29 PM
  3. Slicer: Two Different Data Sources
    By lb0389 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-09-2017, 05:03 AM
  4. Replies: 4
    Last Post: 12-17-2015, 04:20 PM
  5. [SOLVED] Connect multiple pivots (with multiple data sources) to one slicer
    By jeroenft in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-24-2015, 07:29 PM
  6. [SOLVED] Connect multiple pivots (with multiple data sources) to one slicer
    By jeroenft in forum Excel General
    Replies: 1
    Last Post: 08-24-2015, 04:49 AM
  7. Replies: 2
    Last Post: 04-15-2013, 05:33 PM

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