jkp-ads.com Newsletter for February 2017
Slicers: show selected items in a worksheet
Slicers are a nifty way to filter pivottables (and tables as of Excel 2013). But sometimes you need a way to get the selected items in worksheet cells so you can do calculations with them. In this newsletter I show three methods to get that done.
Method 1: The CUBERANKEDMEMBER function
If your pivottable is based on data from a powerpivot model (As of 2013: the data model) or an OLAP cube you can use the CUBERANKEDMEMBER worksheet function to retrieve sliceritems. The trick is to find out which arguments to use for the function:
The first argument must read "PowerPivot Data".
The second argument is a bit simpler as all your slicers internally will start with "Slicer_" so as soon as you start typing Slicer, Excel will show the slicer names to you in the autocomplete dropdown:
The final argument is just an index to the selected item number so should be an increasing number. I have used the row function for this from which I subtract the rownumber of the row that is located above the first cell which contains my CUBE function. Copy this formula down as far as you expect sliceritems.
Method 2: A VBA User Defined Function
If your slicer isn't based on a powerpivot model but on a normal pivot table, the CUBERANKEDMEMBER function does not work. For that situation I have written a user defined function which gets the selected items:
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
GetSelectedSlicerItems = "No items selected"
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
Use this function by entering this formula (array-entered by hitting control+shift+enter after selecting as many cells as you expect slicer items):
Notice that I use the ; as formula argument separator, you might need to use the comma!
Method 3: An additional pivottable
This is the simplest method to get the selected items: simply create an additional pivottable which only contains one row field, the field which is filtered by your slicer. Make sure the pivottable is tied to the same pivotcache as the one the slicer operates on.
More about slicers
Want to learn more about slicers? Read the article on my website
The Amsterdam Excel Summit
Registration has been opened for our 2017 Amsterdam Excel Summit. To spark your interest I have summarized the content we expect to offer below:
April 18th, 2017: Pre-conference day: The Excel Data Analysis And Reporting Masterclass, an entire day of in-depth training by die-hard Excel experts covering these subjects:
April 19, 2017: The Amsterdam Excel Summit. Featuring Excel MVPs from all over the world presenting in their core expertise areas. Also featuring: Product managers from the Microsoft Excel team.
- Streaming datasets in Power BI
- A data-cleansing perspective on Power Query
- Designing your model for charts and dashboards
- Reverse-Engineering an Excel dashboard
- A keynote by Yigal Edery, Microsoft Promgram Manager for Excel's power tools
- Power Query: Automatically convert currencies depending on the date
- The goofy things to watch out for in PowerQuery
- A comparison of Excel Extensibility Technologies
- Excel Power Pivot – Hierarchy and Control tables
- Bing maps & Excel
- Moving beyond Excel with Power BI
- The Checks In The Post
Register now, early bird discount!
Registration is open. Make sure you book your seats before february 20, 2017 and receive a € 50 early-bird discount!
The program will be announced on our event site topexcelclass.com
Two outstanding trainings by ExcelGuru.com
Live Power BI Training
Ken Puls from Excelguru is leading a 3-day, hands-on boot camp February 22 - 24 in Vancouver, Canada that will allow you to hit the ground running with Power BI. Visit http://xlguru.ca/data2dashboards for full course details and registration.
Online Power Query Workshop
The next live, online workshop by the team at Power Query Training is running March 1 – 2. If you use Excel to clean, reshape, transform or combine data then this is the workshop for you. More information and registration available at https://www.powerquery.training.