+ Reply to Thread
Results 1 to 21 of 21

Slicers & Tables - How to link Slicer to mulitple tables

  1. #1
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Slicers & Tables - How to link Slicer to mulitple tables

    Hello,

    I have 3 worksheets: Budget, Forecast & Actuals.

    I want to create a summary at the top of the Budget worksheet that allows user to select Customer/Category/Size. I thought the Slicer tool would work best but then realized I only know how to capture the data from the one worksheet.

    How can I easily capture data from different worksheets with drop down option/filter?

    I have attached a sample workbook.

    Thank you so much!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    My suggestion in the attachment. Try to remodel your data. You have two variants to choose from.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Hi stasinek, thanks so much for your suggestion! However, I was trying to avoid combining the data onto one tab. My real data is actually really long

    Is there any way to use the slicer from 3 worksheets?

    Thanks!

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    In the information window is Ex 2003 and you are using slicers? Probably Ex 2013 so you can safely use Power Query

  5. #5
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    I just installed PowerPivot but have no idea where to start! I definitely want to learn it in the future.

    For now, I think I have no choice but to combine the 3 tabs into 1 then slice and subtotal.

    Thanks for your suggestions!

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Update your profile about actual excel version. will be easier for you and for us
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Thanks Sandy. Just updated

    Do you have a workaround for me without using PowerPivot (right now). I'm about to consolidate the data from the tabs but if you have a suggestion please let me know!

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Let me read whole thread first

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Slicers & Tables - How to link Slicer to mulitple tables

    Is that what you want?
    You can add slicer(s) on summary tab.
    There is "standard" PivotTable

  10. #10
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Did you use the relationship tool?

    When I went to create a relationship, I got the error that said "...relationship in pivot table can only be unique values". I basically gave up at that point. My data tables are identical with repeating values. I just have them created on 3 separate tabs so I can keep track of budget, forecast and actuals.

    Maybe I am making it too complicated and should just consolidate everything onto 1 tab.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Slicers & Tables - How to link Slicer to mulitple tables

    Did you see word "standard" ? I didn't use PowerPivot
    PivotTable on Summary tab sums the values of the three tables

    use PivotTable and PivotChart Wizard

    edit:
    you can change names: Row to Materials and Column to Category
    but you didn't answer: is that what you want to achieve?

    just in case here is extended version
    Last edited by sandy666; 11-24-2017 at 02:27 PM. Reason: extended version

  12. #12
    Registered User
    Join Date
    02-18-2010
    Location
    T.O.
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Thank you Sandy! I'm going to give it a try.

    Thanks again!!

  13. #13
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    With drop down lists it will be like this
    Attached Files Attached Files

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Slicers & Tables - How to link Slicer to mulitple tables

    Or maybe like this one?
    [@Summary2]

  15. #15
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    And it was supposed to be without PQ

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Who said: without PQ?
    Why do I have to complicate my life if it can be simplified?

    Use Power (Query), Luke !

    Please Login or Register  to view this content.
    Last edited by sandy666; 11-24-2017 at 07:40 PM.

  17. #17
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    greentea wrote "Do you have a workaround for me without using PowerPivot (right now)"

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,612

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Quote Originally Posted by stasinek View Post
    My suggestion in the attachment. Try to remodel your data. You have two variants to choose from.
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  19. #19
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    PowerPivot is not a PowerQuery
    PowerPivot is not a standard PivotTable
    and vice versa

    People are afraid of what they do not know and do not understand and they should learn it so the work with Excel would be easier.
    Last edited by sandy666; 11-25-2017 at 09:29 AM.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,612

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    Quote Originally Posted by stasinek View Post
    And it was supposed to be without PQ
    No, the OP asked for it not to be using PowerPivot, not PowerQuery.

  21. #21
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Slicers & Tables - How to link Slicer to mulitple tables

    LOL, I've the support of beautiful Mod

+ 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: 5
    Last Post: 12-04-2017, 10:33 AM
  2. slicers on copied pivot tables
    By stephme55 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-07-2016, 02:07 PM
  3. slicers for sorting pivot tables
    By stephme55 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-11-2016, 03:49 PM
  4. Filter Multiple Tables with one set of Slicers
    By palmer266 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2015, 09:04 PM
  5. Pivot tables/slicers
    By Kittykatinla in forum Excel General
    Replies: 0
    Last Post: 11-06-2014, 02:21 AM
  6. [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
  7. Multiple Data Tables Link and Calculate onto Target Tables
    By billexchry in forum Excel General
    Replies: 6
    Last Post: 03-04-2011, 02:42 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