+ Reply to Thread
Results 1 to 7 of 7

Pull selected value from Slicer

  1. #1
    Registered User
    Join Date
    01-21-2018
    Location
    Northampton, England
    MS-Off Ver
    2010
    Posts
    50

    Pull selected value from Slicer

    Afternoon all,

    I am looking to pull the selected value from the slicer. this will mean that if in the Slicer i have selected UK then the cell on the page will pull that selection and state UK.

    Ideally this needs to be though a formula and not VBA.

    Thanks in advance.

  2. #2
    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,167

    Re: Pull selected value from Slicer

    Sorry - I'm not clear about what you are describing. If you use a slicer with a table, then the slicer will filter the table for you. Is this not what you want?
    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.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,457

    Re: Pull selected value from Slicer

    One way is to create a second pivot table from the first (so they are sharing cache) which only includes the slicer category.

    Copy first pivot table to cell K2, then right click and “Show Field List” and select the category in your slicer. Use the value in K3 in your formulas (I named cell K3 SlicerValue for clarity)

    See example. Any questions?
    Attached Files Attached Files
    ChemistB
    My 2˘

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,457

    Re: Pull selected value from Slicer

    @AliGW,
    I can see a few purposes for doing this.
    1. If the item you’re slicing is not one of your columns on your pivot table, (and your slicer is off screen(, this makes it easy to see how you are manipulating data.
    2. You can use this value to pull data from or manipulate other tables
    .

  5. #5
    Registered User
    Join Date
    01-21-2018
    Location
    Northampton, England
    MS-Off Ver
    2010
    Posts
    50

    Re: Pull selected value from Slicer

    The slicer isn't for a pivot table. Its working from a table and reducing the informat

  6. #6
    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,167

    Re: Pull selected value from Slicer

    Perhaps if you attach a sample workbook showing what you are trying to achieve?

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

    Re: Pull selected value from Slicer

    example file would be a good idea...

    even if the slicer works on a table you can still make a pivottable as described by ChemistB. the goal is that you will have an output of the selection in certain cells you can use somewhere else.

    on top of that there is also the CUBERANKEDMEMBER function if the table is part of a powerpivot datamodel.
    in this article https://www.jkp-ads.com/articles/slicers05.asp there it is explained how that works for power pivot slicers. But it also describes a VBA solution to do it for normal (non power pivot) slicers, I know you say rather not VBA, but it is in the article and I know it works well..

    if slicers dont give you the information you need you might also want to look at an alternative of advanced filter, because then you can type the values in cells under the set filtercriteria range.
    https://support.office.com/en-us/art...8-3f16abdff32b

+ 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. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  2. Replies: 1
    Last Post: 04-23-2018, 09:43 AM
  3. [SOLVED] Random slicer value selected automatically
    By ozstrik3r69 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-16-2018, 12:54 PM
  4. Finding Our Selected Slicer Value
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2017, 07:52 AM
  5. Use selected data from slicer
    By Tjken in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-22-2014, 07:18 AM
  6. Copy selected value from slicer
    By merdle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2014, 04:56 PM
  7. VBA for displaying selected slicer in cell S7
    By crobledo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2013, 03:14 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