+ Reply to Thread
Results 1 to 3 of 3

Help to use Slicer to display (x-ref) data from multiple different columns

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    2

    Help to use Slicer to display (x-ref) data from multiple different columns

    Hi All,

    I have a spreadsheet that contains:

    * Company Name
    * Opportunity Name
    * Rep Name
    * Total Profit.

    There might be several opportunities per company name, with different reps associated.

    I want to be able to select one rep from a list - like a slicer - and have the pivot table show me a list of the companies & ops that rep belongs to - but also all the other reps & their opportunities against that company -so that the reps can see who else is working on their accounts and maybe collaborate.

    I have about 60 reps, so i'd use a sumifs to make a yes/no per rep v company if I had less, but 60 individual columsn on the pivot would just be a disaster.

    Any thoughts?
    Attached Files Attached Files

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

    Re: Help to use Slicer to display (x-ref) data from multiple different columns

    "I get by with the help of my friends." or in this case helper columns and helper pivot tables.

    On the pivot sheet in column O I have a pivot table that shows only company names. This pivot table is connected to the same slicer that controls the main table.

    I overlaid this pivot table with a named dynamic range called List_Company: =OFFSET(Pivot!$O$4,0,0,COUNTA(Pivot!$O:$O)-1,1). Here is some information on named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    Back on the Data sheet, I converted the range of data to an Excel table. Excel tables "know" how big they are so when you add or delete rows, formulas, charts and pivot tables built from them do not have to be changed. They will always reference the exact amount of data they need. Also Excel tables "remember" and copy down formulas automatically. See this wiki for information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

    So when you are ready to use real data, you can remove the data rows (see the wiki on how to do this) and copy in the new data.

    The new helper column Also Rep has the formula: =ISNUMBER(MATCH([@[Account Name]],List_Company,0)) which reads List_Company, which in turn are only those companies associated with the selected name. So this is true if the account name is on the list of companies shown in column O.

    Which brings us to the pivot table in columns L & M. This shows the list of people who are also associated with the companies for the selected person.

    So far so good without VBA. But there is an issue. The pivot table in columns L & M does not refresh automatically. So I added a short bit of code to refresh it when the other tables are refreshed.



    Back on the data sheet, I added this to the
    Attached Files Attached Files
    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-05-2019
    Location
    Brisbane, Australia
    MS-Off Ver
    2016
    Posts
    2

    Re: Help to use Slicer to display (x-ref) data from multiple different columns

    This is very awesome. Thanks a lot, dflak. This is exactly what I wanted.
    I keep thinking I know how to use excel and then every day I learn that I have a long, long journey ahead.

    I'll put some time aside today for the offset & dynamic range training. I think I'll need to book out a couple of hours:

    This is going to be a tough lesson for three reasons:

    Its perceived level of difficulty (Commands with as many as 5 arguments can be intimidating, but it’s not difficult to understand if you take it one step at a time.)
    It’s a long lesson
    You won’t see an immediate need (“That’s nice, but what does it do for me?”) Trust me, it will make life in Excel better later on.

    ...Awesome.


    Thanks again!

    D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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