Copy

Newsletter by jkp-ads.com

View this email in your browser

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
    Application.Volatile
    Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        Err.Clear
        Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
        If Err.Number > 0 Then
            Set oSItems = oSc.SlicerItems
        End If
        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
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                If ReturnArray Then
                    GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
                End If
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

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:
  • Streaming datasets in Power BI
  • A data-cleansing perspective on Power Query
  • Designing your model for charts and dashboards
  • Reverse-Engineering an Excel dashboard
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.
Subjects include:
  • 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
  • Office JavaScript (By Microsoft Program manager)

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!

Program

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.

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.
 

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility
.
 
Copyright © 2017 JKP Application Development Services, All rights reserved.


unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp