Rename Slicer names for easier use in Excel Cube functions
OR The easier way – Use the Name manager in the Formulas Tab – Thanks for the tip Milang
When working with PowerPivot the feature of inserting slicers to filter your pivot tables is quite handy.
But sometimes pivottables can’t perform the layout you want and you use the Excel CUBE functions instead, and in those formulas you can refer to the slicers by their name.
In this case Slicer_Month.
But sometimes you insert the same dimension as a slicer several times and modify which pivot tables they should be linked to and then you end up with slicers having names
In order to make it easier to construct your functions it would be more appropriate to call the second Slicer_LastYear.
There is no way to modify this via the userinterface but it can be modified via VBA and the Excel Object model.
But this simple code in a userform do the trick
Dim objSlicer As SlicerCache
Private Sub cmdCancel_Click()
Private Sub cmdOK_Click()
objSlicer.Name = Me.txtSlicerName.Text
Private Sub UserForm_Initialize()
On Error Resume Next
Set objSlicer = ThisWorkbook.ActiveSlicer.SlicerCache
On Error GoTo 0
If Not objSlicer Is Nothing Then
Me.txtSlicerName.Text = objSlicer.Name
MsgBox “No slicer selected”, vbExclamation
Me.txtSlicerName.Enabled = False
So select a Slicer and run the form
And you can rename the slicer name to use in functions.
And now the slicer can be referenced via the name you give it.
If you want a copy of the model you can download it here – https://skydrive.live.com/redir.aspx?cid=093b838bfb55945e&resid=93B838BFB55945E!3317&parid=93B838BFB55945E!305