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.
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!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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?
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
@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
The slicer isn't for a pivot table. Its working from a table and reducing the informat
Perhaps if you attach a sample workbook showing what you are trying to achieve?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks