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

Option Explicit

Dim objSlicer As SlicerCache

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub cmdOK_Click()

objSlicer.Name = Me.txtSlicerName.Text

Unload Me

End Sub

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

Else

MsgBox “No slicer selected”, vbExclamation

Me.txtSlicerName.Enabled = False

Exit Sub

End If

End Sub

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

7 thoughts on “Rename Slicer names for easier use in Excel Cube functions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s