Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Show Selected Items

Showing selected Slicer items on a worksheet

Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell. If your pivottable is built using a powerpivot model, then you can access this information by using the CUBERANKEDMEMBER worksheet function. If however the slicer was built off of a "normal" pivottable, then you need a bit of VBA code in a User Defined Function (UDF).

The slicer name

In order to retrieve the selected slicer items we need slicer's internal name, both for the CUBERANKEDMEMBER function and for the UDF. This name can be shown in the userinterface by right-clicking the slicer in question and selecting "Slicer settings". The name you are looking for is marked in red in the screenshot below and indicated by "Name to use in formulas":

Slicer settings dialog, needed name highlighted in red

CUBERANKEDMEMBER (for powerpivot slicers)

The CUBERANKEDMEMBER function returns an array of selected items and hence needs to be into as many (vertical) cells as you expect will be selected in the slicer. See the screenshot below (note the formula has ; as the delimiter, many users need to use the comma instead).

Using the CUBERANKEDMEMBER function

Note, that when less items are selected than the number of cells the function was entered into, the excess cells will show #N/A.

These #N/A's can be hidden with:

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")

or counted with

=IF(ROW()-ROW($G$5)<=CUBESETCOUNT(CUBESET("PowerPivot Data",Slicer_Name)),CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")

The UDF

The code sample below must be placed in a normal module:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

The function takes the slicername as its argument and returns a text string (comma delimited) containing the selected items. In a worksheet cell the formula would look like this:

=GetSelectedSlicerItems("Slicer_TeamID2")

Conclusion

Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find Slicers a real gem in Excel 2010 and 2013. A great addition to the product!

 


 


Comments

All comments about this page:


Comment by: Jamie M. (4/8/2014 9:09:13 PM)

Your UDF solution for showing slicer selections is AWESOME...thank you so much!

 


Comment by: David H. (7/24/2014 7:25:49 PM)

Really enjoy learning about more indepth functionality that can extracted from Excel. Thanks for sharing. Could you provide UDF code that would put the data in a list format (similar to CubeRankMember function) vs a delimited format?

Thanks

 


Comment by: Jan Karel Pieterse (8/12/2014 11:07:16 AM)

Hi David,

Try this modified UDF:

Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False) As Variant
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            ElseIf oSi.HasData = False Then
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
                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

 


Comment by: Shanker (10/20/2014 1:49:37 PM)

Hi -

Thanks for such a wonderful function, it works perfectly fine with ordinary pivots and slicers, however, when i use PowerPivots, it throws application defined or object defined error on "For Each oSi In oSc.SlicerItems", I am not sure, why it works fine with ordinary pivots and when i am using powerpivot in excel 2103, it fails.

Would appreciate any thoughts/help, you may have.

thanks in Advance,
Shanker

 


Comment by: Jan Karel Pieterse (10/20/2014 2:11:34 PM)

Hi Shanker,

That is why I show how to do this with a Powerpivot model using the CUBERANKEDMEMBER function.

 


Comment by: Jeffrey (2/11/2015 3:56:12 PM)

Just wanted to say thanks...your code was exactly what I needed to retrieve the slicer item selected by the user and then apply that to a heading filter. I can do this naturally in Excel 2013, but my company is still using Excel 2010.

 


Comment by: Maria (3/31/2015 1:03:16 PM)

How would you modify the vba to display only available items?

For example:

Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY.

If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer.

How do I list those available items?

Thank you.
Maria

 


Comment by: Maria (3/31/2015 1:17:48 PM)

Never mind. I figured it out.

Thank you for your code and your time.

Maria

 


Comment by: Jan Karel Pieterse (3/31/2015 2:46:45 PM)

Hi Maria,

It is always nice when people solve their own problems!

 


Comment by: Simon (4/21/2015 10:00:04 AM)

Maria asked:

> Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY.
>
> If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer.

Then replied that it had been solved.

@Maria/JKP: How did you solve it? I have a similar requirement:

1) display in a comma-delimited list all slicer items that are still valid (not grayed out) after any filter is changed.
2) if all items are still valid it should show the entire list.
3) if no filter is active it should default to "no active filters" or similar insstead of listing every item in that slicer.

Example:

Slicer 1 'Flavor': Apple, Banana, Cherry
Slicer 2 'Grocery': Yogurt, Candy, Soda
Slicer 3 'Brands': ABCfood, BestCo, CoolSnacks

Slicer 1 ('Flavor') has value "Apple" selected, which is captured by the "selected items" method on this page. All items in Slicer 2 are still in scope so the results list cell for 'Grocery' category should display "Yogurt, Candy, Soda". Only one item in Slicer 3 is still in scope so that cell should display 'BestCo', like this:

Selection(s) and current scope
---------------------------------------
[Label Column     | Data Column        ]
[---------------------------------------]
[Flavor Selection: | Apple             ]
[Grocery in scope: | Yogurt, Candy, Soda]
[Brands in scope: | BestCo             ]
---------------------------------------

 


Comment by: Pooja Sivadas (6/5/2015 9:43:50 AM)

The UDF code is just amazing. I have been searching for something like this since a long time. Thank you soo much.

 


Comment by: Pooja Sivadas (6/16/2015 7:17:54 AM)

Hi, there is one problem I'm facing when I use the UDF code. I pasted the code in one of my modules and it works fine.I have a few other Sub routines in my other modules. When I run some of them, on reaching an autofilter statement they are getting automatically redirected to this function but I'm not able to figure why this is happening. Can you please help me regarding the same. Why will a sub get redirected to a function without being called?
Regards,
Pooja

 


Comment by: Jan Karel Pieterse (6/16/2015 5:15:28 PM)

Hi Pooja,

This is because when you cause a recalc during debugging code, Excel's calculation engine will make calls to the User Defined functions and you will be stepping through them each time the function is called.

You can get through the UDF calls in one keystroke during debugging by hitting control+shift+F8.

 


Comment by: Pooja Sivadas (6/18/2015 6:55:49 AM)

That's great Jan. It works. Thanks.

Does that mean the redirecting happens only when we run the code line by line (F8)? I'm worried that the redirection is slowing down my other subs when I run them at once (using F5) as well.

 


Comment by: Jan Karel Pieterse (6/18/2015 10:31:50 AM)

Hi Pooja,

No it just means that whenever your code triggers a calculation that hits cells with your UDF, the UDF gets called for those cells.

 


Comment by: Pooja Sivadas (6/18/2015 11:01:57 AM)

Oh! So that means it will slow down my subs right? Is there any way I can prevent this?
(Can't use Application.Calculation=xlCalculationManual as my code depends on some of the calculated fields from the worksheet)

 


Comment by: Jan Karel Pieterse (6/18/2015 4:02:37 PM)

Hi Pooja,

You could add a switch to your UDF that turns it off, but that will probably cause their calling cells to get wrong results as well. You could control whether an individual worksheet is allowed to recalc perhaps?

 


Comment by: Hans Henriksen (1/9/2016 10:30:33 PM)

Great code, I was searching for this for days, but the UDF does not work with XP SP3 and Excel 2010, it gives runtime error 1004. However, I got it to work by adding an extra object:


Dim osc2 as Object
Set osc2 = osc.SlicerCachelevels(1)
For each oSi In osc2.SlicerItems
    If oSi.Selected Then
     GetSelectedSlicerItems = GetSelectedSlicerItems & TrimSlicerItem(oSi.Name) & ", "
     lCt = lCt + 1
    End If

..
and If lCt = osc2.SlicerItems.Count then

TrimSlicerItem extract the 'data' from the output that looks like this: [table].[column].&[data] and the else clause had to be deleted to count lCt correctly in my test.

 


Comment by: Brandon (1/11/2016 4:20:12 PM)

Hi Jan,

Also wanted to say fantastic solution to this problem.

However, is it possible to get this to work with multiple slicers? I have four slicers and I'd like each one to have its selected slicers appear in a different cell.

I've managed to have all four slicers show their selected items in a single cell but this looks cluttered and awkward.

 


Comment by: Jan Karel Pieterse (1/11/2016 5:28:23 PM)

Hi Brandon,

Here is an alternative version which has an extra argument with which you can choose to either get all items in a single cell, or return a list of them. For the latter to work you must array-enter the formula into as many cells as you expect to have sliceritems.

Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False) As Variant
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            ElseIf oSi.HasData = False Then
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
                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

 


Comment by: Brandon (1/12/2016 7:56:01 PM)

Hi Jan,

Thanks for the amazingly fast reply! One thing I didn't realize in your original code is that SlicerName is not supposed to be replaced (I put the name of one of my slicers in there). Sussed that out and everything works great with your modification. Thank you so much!

 


Comment by: Jan Karel Pieterse (1/13/2016 8:26:01 AM)

Hi Brandon,

You're welcome!

 


Comment by: Diogo (1/31/2016 2:05:17 PM)

Hi Jan,

Thanks so much for your explanation. It is been very exciting to learn all about slicers.
I have a problem that should be simple.

I have 4 slicers connected to the same pivot table and would like to pass on to GetSelectedSlicerItems only the slicer that actually had an item selected.

How can i do this? Is there any how to find out which of the slicers triggered the Workbook_SheetPivotTableUpdate?

 


Comment by: Jan Karel Pieterse (2/1/2016 9:57:13 AM)

Hi Diogo,

I'm afraid there is no simple way to detect which slicer was clicked. The only way I would know is to keep score of which filtes were applied and then try to derive the clicked slicer from the newly applied filters. Given that a click on one slicer may als affect what is displayed on another slicer, this is not easy at all.

 


Comment by: JHN_DK (2/5/2016 3:04:24 PM)

Hi Jan,
Very useful comments, thanks.

I tried following of your macro's:
Public Function GetSelectedSlicerItems... etc,
and that works as expected. BUT my issue is that it only works if I do the selection in the slicer itself. Meaning that the =GetSelectedSlicerItems does not give the result when the sliceritems are reduced as impact from slections in other slicers.
Any suggestions?

Thanks in advance, JHN_DK

 


Comment by: Jan Karel Pieterse (2/5/2016 3:49:19 PM)

Hi

CHeck out this version, which has an additional parameter that you can set to true or false to either ignore or not ignore items that other slicers have filtered out:

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 lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            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 = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
                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

 


Comment by: JHN_DK (2/8/2016 11:56:42 AM)

Hi Jan,

Thanks, just what I was looking for.
I must say: super solution and super quick reply :-)


 


Comment by: Jan Karel Pieterse (2/8/2016 1:42:56 PM)

Hi Joakim,

You're welcome!

 


Comment by: JHN_DK (2/8/2016 3:58:39 PM)

Jan,
Do you also have a trick on how to load that list of "GetSelectedSlicerItems" into another slicer (to synzhronize two slicers with different data sources)?

It's just a long shot as that would save me a lot of time.
Thanks in advance, JHN_DK

 


Comment by: Jan Karel Pieterse (2/8/2016 4:08:41 PM)

Hi Joakim,

No, but you should be able to use the example code above to get this working?

 


Comment by: Aura Reyes (2/29/2016 2:21:08 PM)

Hi Jan,

Thats exactly I was looking for.... but I found an issue. It Works perfectly in a pivot table of a local data base in Excel, but when I tried to do it in another pivot conected to an SQL Data Base through an ODBC conection in Power Pivot shows the follow message: "Not Ítem Selected"

Can you help me please !!!!


PD. Sorry for my bad english, :-)

 


Comment by: Jan Karel Pieterse (2/29/2016 6:03:21 PM)

Hi Aura,

SLicers tied to Powerpivot pivottables or to the Excel 2013/2016 datamodel behave different. I have made a modification to the function which now should work for both cases:

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

 


Comment by: Ivan (5/3/2016 10:53:25 PM)

This is so freakin' helpful! This totally just saved me when no other solution seemed viable. Thanks!

 


Comment by: Wolgrand (5/23/2016 12:33:01 AM)

Hi guys. All wise tips. But Does any one have clue to work with 3 Slicers and the cell just return the last selected item ?

Best Regards

 


Comment by: Jan Karel Pieterse (5/23/2016 9:27:15 AM)

Hi Wolgrand,

It shouldn't be very hard to get the last valid entry from the UDF formula result using normal worksheet functions. Perhaps:

=INDEX(A1:A10,MAX(IF(ISERROR(A1:A10),0,ROW(A1:A10))))

A1:A10 is the precise area the UDF is array-entered into.

(array entered using control+shift+enter)

 


Comment by: Will Swan (7/6/2016 2:43:37 PM)

NewB here. I got the function to return all items. However, it still says "all items" when I click on the selection in the called slicer using : =GetSelectedSlicerItems("Slicer_Response.")

At this point, I have changed no coding. Thoughts?


 


Comment by: Jan Karel Pieterse (7/7/2016 10:29:48 AM)

Hi Will,

Possibly you have the wrong slicer name?

 


Comment by: William Swanigan (7/7/2016 4:45:11 PM)

When I changed the name: =GetSelectedSlicerItems("Slicer_Response3.")

the results given are: "No slicer with name 'Slicer_Response3.' was found"

So, I believe the naming convention was correct.

 


Comment by: Jan Karel Pieterse (7/8/2016 7:54:36 PM)

Hi William,

The reason your code fails is that the function was written with pivot table slicers in mind, not Table slicers. I'll put this on my backburner to add to the article!

 


Comment by: Jagdish Negi (7/31/2016 3:55:49 PM)

I have used the below formula for copying the selected I team from to excel however I am getting error #Name?.

CUBERANKEDMEMBER("PowerPivot Data",Slicer_Brand,ROW()-ROW($E$4))

if I am using the same slicer name with in UDF then I am getting output but that UDF function is giving me coma separated output which I don't want. I need the output in the next Cell instead of coma separated...

Thanks

 


Comment by: Fred Stimler (8/4/2016 9:42:25 PM)

I am using Power Pivot and Excel 2016. I wish to have 4 pivot tables linked to several slicers and I think I know how to do that. I need to filter each pivot table to ONE record - all the same - the top record picked by the slicers. I then want to MOVE from first record to next..to last, etc. all while seeing only one record per pivot table.

I know this is strange by I am simulating an existing report that printed takes 1/2 of a page.

Any ideas will be helpful. I like your lessons.
Fred

 


Comment by: Jan Karel Pieterse (8/9/2016 9:21:36 AM)

Hi Fred,

Perhaps this is a question we'd better discuss via email? My address is down below at the bottom of this page.

 


Comment by: Geoff Lilley (8/12/2016 5:56:29 PM)

I love what you've done. I made a modified version, where I pass function arguments for:
* What message to display for "multiple items"
* How many items constitutes "multiple?"
* What message to display for "all items"

Couldn't have done it without you. Thank you.

 


Comment by: Jake (9/16/2016 2:03:06 AM)

It may be that the fault is internal to my cranium - I am getting '#Name' - I have made sure I am using 'the Name to use in formulas' Apologies if this is a no brainer but have tried a few things and am not making progress - any help would be greatly appreciated

 


Comment by: Jan Karel Pieterse (9/16/2016 2:59:22 PM)

Hi Jake,

If you start entering the slicername in the formula, you should get Excel's intellisense dropdown showing you which slicer caches it has available for you.

 


Comment by: Justin (9/20/2016 12:41:46 AM)

I'm getting the same error message as Jake no matter what I enter as the slicer name - either by typing the name in quotes or selecting the name from the dropdown.

 


Comment by: Jan Karel Pieterse (9/20/2016 6:51:40 AM)

Hi Justin,

What if you run this little macro from your workbook and try the names listed:

Sub EnumSlicers()
    Dim oSc As SlicerCache
    For Each oSc In ThisWorkbook.SlicerCaches
        Debug.Print """" & oSc.Name & """"
    Next
End Sub

 


Comment by: marksu (9/23/2016 9:58:09 AM)

hey Jan

do you know how one can change the "name to be used in formulas" in VBA?

somehow it works when I hardcode a string into the right place after the slicers.add function, but not when I use a string variable.

any idea why this is the case?

Kr
Markus

 


Comment by: Jan Karel Pieterse (9/23/2016 2:08:07 PM)

Hi Mark su,

Have you got any example code to share? Even if it isn't working?

 


Comment by: markus (9/23/2016 2:18:46 PM)

Yeah sure :)

here you go:

            .Parent.PivotCaches.Create SourceType:=xlDatabase, SourceData:=transfer).CreatePivotTable _
            TableDestination:=DestinationRange, TableName:=tblName
            .Parent.SlicerCaches.Add(.PivotTables(tblName), "CVP", "asdf").Slicers.Add ActiveSheet, , "CVP", "CVP", _
            1000, 800, 150, 200


these are the two lines, where I set up the pivot- and slicercache

like posted it works but if I change "asdf" to a variable like slcName it throws an error.

Kr
Markus

 


Comment by: Jan Karel Pieterse (9/23/2016 4:36:37 PM)

Hi Markus,

Seems to me that this should work:


Dim sSlicerName As String
sSlicerName = "fdsa"
.Parent.SlicerCaches.Add(.PivotTables(tblName), "CVP", sSlicerName).Slicers.Add ActiveSheet, , "CVP", "CVP", _
1000, 800, 150, 200

 


Comment by: Ashley (9/23/2016 5:25:39 PM)

This is great! However, when using multiple slicers, if I then go on to choose a slicer within the original selection the cell link refreshes to All Items and my formulas no longer work. Is there a solution for this?

Ashley

 


Comment by: Markus (9/23/2016 5:41:15 PM)

Hi Jan

Thanks for your reply. I also thought that should do the trick. But somehow it doesn't work.

I just omitted the name attribute now, so there's no issue with that but the next time I iterate over this line it says "invalid procedure". When I go into debug mode and hit F8 to resume, it says "this slicercache already exists". It actually creates the slicercache but still throws an error.

Any idea how I can resolve this?

Kr
Markus

 


Comment by: Jan Karel Pieterse (9/26/2016 10:14:02 AM)

Hi Markus,

Perhaps you can email a copy of the file?

 


Comment by: Jan Karel Pieterse (9/26/2016 10:15:14 AM)

Hi Ashley,

I'm not sure what you mean?

 


Comment by: Ashley (9/26/2016 2:09:58 PM)

Hi Jan,

Apologies, it's quite difficult to explain! I have a file containing costing data to be sent to various contacts but to be protected for their region only.

I'm using your solution in a landing page for the contact to choose their area and enter a password. This then loads the data into a separate tab. However, there are multiple dependent slicers on this tab in which they can delve deeper into the data at site level for example.

However, when they do this the formula on the landing page refreshes to display "All Items" throwing my password and formula off.

Hope that makes sense and really grateful if you have a solution at all!

Thanks, Ashley

 


Comment by: Jan Karel Pieterse (9/26/2016 5:56:25 PM)

Hi Ashley,

I'm afraid I'd need your file and some instructions on how to reproduce your problem.

 


Comment by: Ashley (9/27/2016 10:32:53 AM)

Hi Jan,

I'll have to create an example file as the data is confidential. How would I share this with you?

Thanks,
Ashley

 


Comment by: Jan Karel Pieterse (9/27/2016 11:20:53 AM)

Hi Ashley,

You can send it to as a reply to the confirmation message you got stating your reply was approved.

 


Comment by: Leo Meijer (11/4/2016 8:56:46 AM)

Hello all

In a PivotTable, you can click after filtering the filtered items, allowing you to see the underlying data will take on a new tab.

Now I use multiple slices to filter a PivotTable.
But I do not get the "slicers with filtered data" to see a new tab.

Does anyone have an idea how I wanted to see the filtered data or receive?

Thank you very much

Leo Meijer

 


Comment by: Jan Karel Pieterse (11/14/2016 4:30:39 PM)

Hi Leo,

I assume your problem is that the data that gets dumped on the new tab is not filtered according to your slicer filters, correct? This happens to fields with slicers that are NOT a field in the pivottable. TO ensure you get filtered drill-down results, make sure your slicer fields are all in the pivottable (add them as page fields for instance).

 


Comment by: Paul Bell (11/24/2016 3:47:18 PM)

Hi,

I have a workbook with 3 sheets, "Complaint Chart", "Pivot Tables" and "Pivot Charts".

The actual pivot tables, on the sheet Pivot Tables, are created from a dynamic named range from the Complaint Chart sheet.

I then have 3 slicers, Year, Day and Type on the Pivot Chart sheet which are linked to all my pivot tables.

The problem I have is when the userform adds new data to the Complaint Chart sheet the pivot tables refresh ok but the slicers appear to duplicate the 'filter options'. For example, The complaint chart sheet contains many rows containing the year 2016, however when new data is added to the complaint chart, with year 2016, the pivot tables refresh ok but the slicers then show 2016 twice in the 'filter options'.

I cant figure out why this is happening or how to stop it, please can anyone help?

Thanks in advance. Paul.

 


Comment by: Jan Karel Pieterse (11/25/2016 2:33:22 PM)

Hi Paul,

I strongly suspect there are both 2016 viewed by Excel as numbers and 2016 viewed by Excel as text.

 


Comment by: Joey (11/30/2016 4:41:52 PM)

Hi, I have a pivot table with seven slicers and I used the UDF and it worked perfectly for six of the slicers. However, for the 7th one it always says "No items selected" regardless of if I have items selected or not.

Not sure if this matters or not, but that slicer has a lot of items in it (over 10,000).

Does anyone know why this is happening?

Thanks.

 


Comment by: Jan Karel Pieterse (12/6/2016 10:08:37 AM)

Hi Joey,

I don't know. Perhaps that slicer has other differences?

 


Comment by: Mitch Hollberg (2/22/2017 5:59:54 PM)

Fantastic work! Thanks so much for posting this.

 


Comment by: William Linn (2/24/2017 9:28:28 AM)

This code is great, saved me lots of time, Thanks for your efforts.

 


Comment by: Johan Karldén (3/7/2017 4:48:03 PM)

This is a very good script, but what about if you use the data model as source? I can't get it to work with a pivot table created from PowerPivot. How can you fix that?

 


Comment by: Jan Karel Pieterse (3/7/2017 4:52:38 PM)

Hi Johan,

Have a look at this comment:
http://www.jkp-ads.com/Articles/slicers05.asp?AllComments=True#23919

 


Comment by: Ben (3/10/2017 5:28:21 AM)

Script works wonders when selecting a GROWING number of slicer items.

But, if in sequence:
All items are selected, Enable Multi Select, deselect any slicer item

Items with no data then show up in output.

Any idea why this might be happening?

 


Comment by: Jan Karel Pieterse (3/10/2017 11:39:27 AM)

Hi Ben,

Odd, seems to work fine for me. Can you send an example perhaps?

 


Comment by: K1S (3/14/2017 9:11:20 AM)

@Geoff Lilley

Hi Geoff,

re: http://www.jkp-ads.com/Articles/slicers05.asp?AllComments=True#24354

Can you share those modifications please?

Thanks

 


Comment by: Jan Karel Pieterse (3/14/2017 9:50:37 AM)

Hi K1S,

To set expectations: I don't expect Geoff is likely to notice your post as I don't send out any notifications to anyone else than the person who posted a comment.

 


Comment by: ks (4/5/2017 11:34:59 AM)

Hi, I'm trying to adapt this so that it doesn't show a comma after a single or the last entry, and also that it can show "Multiple selected" if there are more than a given number of selected options, but I'm struggling to get it right. Could somebody help me please?:

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
        
            Select Case lCt
            Case Is = oSItems.Count
                GetSelectedSlicerItems = "All"
'            Case Is > 3
'                GetSelectedSlicerItems = "Multiple"
            Case Else
                If ReturnArray Then
                    GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
                End If
            End Select
    
        Else
            GetSelectedSlicerItems = "None selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

 


Comment by: Jan Karel Pieterse (4/5/2017 11:51:08 AM)

Hi KS,

To remove the last comma (and space), just before the

Select Case lCt

add:

GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)


To get "multiple items", make sure the Select case looks like:

        
            Select Case lCt
            Case Is = oSItems.Count
                GetSelectedSlicerItems = "All"
            Case Else
                If ReturnArray Then
                    GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
                Else
                    If lCt>3 Then
                        GetSelectedSlicerItems = "Multiple"
                        'No Else needed, as the string has already been assigned above
                    End If
                End If
            End Select


 


Comment by: ks (4/5/2017 12:11:45 PM)

Hi Jan, many thanks for your swift reply.

I have it now like this:

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
        
            Select Case lCt
            Case Is = oSItems.Count
                GetSelectedSlicerItems = "All"
            Case Else
                If ReturnArray Then
                    GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
                Else
                    If lCt > 3 Then
                        GetSelectedSlicerItems = "Multiple"
                    End If
                End If
            End Select
    
        Else
            GetSelectedSlicerItems = "None selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

...but it seems to make everything multiple (i.e. even if I have only 1 or 2 selected in the slicer)

 


Comment by: Jan Karel Pieterse (4/5/2017 5:23:02 PM)

Hi ks,

Try if setting CountNoDataItems to false in the worksheet function helps.

 


Comment by: ks (4/6/2017 8:23:09 AM)

Hi setting CountNoDataItems to false makes "Multiple" work, but now "All" doesn't work

 


Comment by: Jan Karel Pieterse (4/6/2017 11:28:59 AM)

Hi ks,

HOw does this verison work for you?

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"
            ElseIf lCt > 3 Then
                GetSelectedSlicerItems = "Multiple 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

 


Comment by: ks (4/7/2017 10:10:59 AM)

Hi Actually it still doesn't work. With CountNoDataItems set to false it doesn't show "Multiple" items if lCt >3, and when set to true it shows "Multiple items" even if only 1 or 2 are selected.

 


Comment by: Jan Karel Pieterse (4/12/2017 11:32:43 AM)

Hi ks,

Apologies for the delay, I've been busy :-)

This appears to work for me:

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
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSItems.Count Then
                GetSelectedSlicerItems = "All Items"
            ElseIf lCt > 3 Then
                GetSelectedSlicerItems = "Multiple 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

 


Comment by: Leona (4/14/2017 10:13:45 PM)

Hi, I have four slicers linked to a pivot table. I need to count the number of selected items in one slicer, after items from the other slicers have been selected. Is there an easy way to do this? Thanks!

 


Comment by: Jan Karel Pieterse (4/17/2017 8:08:53 PM)

Hi Leona,

You could use the getselectedsliceritems function and use a regular worksheet function to count the returned number of text entries?

 


Comment by: Asha (4/19/2017 4:24:21 PM)

How do I link a slicer to a pivot such that when I click the slicer(it is the first level of row labels as well) the pivot chart should expand to display the next level of row labels?

 


Comment by: Jan Karel Pieterse (5/9/2017 3:51:29 PM)

Hi Asha,

Slicers only apply filtering to a pivottable, they do not control expansion of pivot items.

You could use VBA, more precise the Workbook_SheetPivotTableUpdate event in ThisWorkbook which gets triggered if you click a slicer and then figure out what to expand.

 


Comment by: Lasse Jensen (6/27/2017 4:15:38 PM)

Just copy/pasted the code from this thread :

Comment by: Jan Karel Pieterse (2/29/2016 6:03:21 PM)
http://www.jkp-ads.com/articles/slicers05.asp?AllComments=True#23919

But I would like to skip the information related to the slicer name in the result, and only show the last part, this being the filtered value in question

[Customer Geography].[Geography Country Name].&[United Kingdom],

My PivotTable and slicer is based upon a data connection to SQL Server Analysis Services Cube structure

 


Comment by: Jan Karel Pieterse (6/27/2017 4:44:16 PM)

Hi Lasse,

You could change this line of code:

GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","


to:

GetSelectedSlicerItems = GetSelectedSlicerItems & Mid(oSi.Name, InStrRev(oSi.Name, ".") + 1, Len(oSi.Name)) & ","

 


Comment by: Lasse Jensen (6/27/2017 4:50:47 PM)

How about getting rid of the additional &[] in the filter value as well ???

 


Comment by: Jan Karel Pieterse (6/27/2017 5:39:34 PM)

Hi Lasse,

GetSelectedSlicerItems = GetSelectedSlicerItems & Replace(Replace(Mid(oSi.Name, InStrRev(oSi.Name, ".") + 1, Len(oSi.Name)),"&[", ""), "]", "") & ","

 


Comment by: Lasse Jensen (6/28/2017 10:29:19 AM)

Hi Jan

This is awesome, - works perfectly, and a much better solution than the one CUBERANKEDMEMBER offers

Many thanks
Lasse

 


Comment by: Jan Karel Pieterse (6/28/2017 10:29:49 AM)

Hi Lasse,

Nice to hear you like it!

 


Comment by: Biprajit goswami (6/30/2017 12:49:21 PM)

I tried running the above function with the slicer names suggested by excel intellisense but evrytime i get the output no slicer with name was found.
Could you please help resolve this problem?
Thank you

 


Comment by: Jan Karel Pieterse (6/30/2017 3:29:36 PM)

Hi Biprajit,

If you right-click the slicer and select Slicer settings the dialog shows two names. The name you need is the "Name to use in formulas"

 


Comment by: Biprajit goswami (7/6/2017 11:30:19 AM)

The name to be used in formulae is the name suggested by the intellisense. Even using that in the function gives me the same result " no slicer with name was found"

 


Comment by: Jan Karel Pieterse (7/6/2017 2:03:58 PM)

Hi Biprajit,

You did put the slicername in quotes, like I show above?

 


Comment by: Biprajit (7/6/2017 7:03:25 PM)

Thank you sir..after i put the name within quotes, i got the correct slicer selection.

 


Comment by: Geoff Lilley (7/7/2017 11:53:01 AM)

The modifications I made are as follows:


Public Function GetSelectedSlicerItems(SlicerName As String, multiItems As Long, Optional tooMany As String) As Variant
    If tooMany = "" Then tooMany = "Multiple Items"
    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 >= multiItems Then
                GetSelectedSlicerItems = tooMany
            ElseIf lCt = oSItems.Count Then
                GetSelectedSlicerItems = allItems
            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
    If Right(GetSelectedSlicerItems, 1) = "," Then GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 1)
    GetSelectedSlicerItems = Replace(GetSelectedSlicerItems, ",", ", ")
End Function

 


Comment by: Jan Karel Pieterse (7/7/2017 11:54:07 AM)

Hi Geoff,

Thanks! I managed to publish your comment by removing some lines of text. TBH I don't know what triggered the spam filter :-)

 


Comment by: Gale (7/18/2017 5:30:35 PM)

A noob here, whatever I do I only get the first value in each and every cell, could you show how the array formula looks for this and what cells does it refer to?

 


Comment by: Jan Karel Pieterse (7/18/2017 9:45:06 PM)

Hi Gale,

You must select a bunch of cells and then control shift enter the formula.

 


Comment by: Biprajit goswami (7/19/2017 10:57:59 AM)

Sir, how do i modify the function if i have multiple slicers(6) and i want the selection name in one destination cell?

 


Comment by: Biprajit goswami (7/20/2017 11:31:24 AM)

Sir
If i have say 6 slicers and i want my selection from these slicers in one destination cell, what modification i have to do?

 


Comment by: Cesar (8/1/2017 8:16:31 PM)

I have the GetSelectedSlicerItems code working. However, How do I get the values of the selected slicers values from the power pivot measures? I am using cubevalue. It returns values for single items. I don't know what formula to use for multiple selected items.
This is the formula I was using.
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[FY18 Budget $]","[Table4].[Allocation Category].[All].["&E$10&"]").
It would pull E10 data if it doesn't include ",". But, I would like to pull data for multiple selected items.

 


Comment by: Jan Karel Pieterse (8/13/2017 5:04:57 PM)

Hi Biprajit,

You simply use the function pointing it to the cell containing the slicername:
=GetSelectedSlicerItems(A2)

 


Comment by: Jan Karel Pieterse (8/13/2017 5:10:21 PM)

Hi Biprajit,

I would simply join them using 6 function calls:

=GetSelectedSLicerItems("Slicer_1")&", "&GetSelectedSLicerItems("Slicer_2)&", "&GetSelectedSLicerItems("Slicer_3)&", "&GetSelectedSLicerItems("Slicer_4)&", "&GetSelectedSLicerItems("Slicer_5)&", "&GetSelectedSLicerItems("Slicer_6)

 


Comment by: Jan Karel Pieterse (8/13/2017 5:17:16 PM)

Hi Cesar,

You have to select as many cells (n=below each other) as you are expecting selected values. Then you enter your formula into those cells as one array formula by pressing control+shift+enter.

 


Comment by: Jeff Weir (8/28/2017 4:49:53 AM)

Hi Jan Karel. I see that there is a .VisibleSlicerItems property that you can directly iterate over, which will be faster than checking the .visible status of each and every .SlicerItem in the cache.

I'm using this:

Public Function SlicerItems(SlicerName As String, Optional sDelimiter As String = "|") As String

    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim i As Long
    Dim lVisible As Long
    Dim sVisible() As String
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        With oSc
            If .FilterCleared Then
                SlicerItems = "(All)"
            Else
                lVisible = .VisibleSlicerItems.Count
                If .VisibleSlicerItems.Count = 1 Then
                    SlicerItems = .VisibleSlicerItems(1).Name
                Else
                    ReDim sVisible(1 To lVisible)
                    For i = 1 To lVisible
                        sVisible(i) = .VisibleSlicerItems(i).Name
                    Next i
                    SlicerItems = Join(sVisible, sDelimiter)
                End If
            End If
        End With
    Else
        SlicerItems = SlicerName & " not found!"
    End If
    
End Function

 


Comment by: Alex (9/11/2017 4:22:19 PM)

Hi,

This code works Great, thank-you! In my slicer I'm filtering Dates, If I wanted the cell result to show a date range instead of each date (ie. 1-Jan-17,2-Jan-17,3-Jan-17) would show as "1-Jan-17 thru 3-Jan-17" would that be possible?

 


Comment by: Jan Karel Pieterse (9/11/2017 5:13:32 PM)

Hi Alex,

One way I can get Excel to do that is by actually grouping the dates on a fixed number of days.
Another would be to add a column which calculates the "period" from the date column and use that for the slicer.

 


Comment by: Lucas Coy (11/20/2017 6:42:50 AM)

Hi Jan,

First of all, your code works fantastic! I do have a question though, is it possible to make it work on a "timeline" slicer? I've used your UDF on the timeline that I have in my sheet but it always returns "No Items Selected" even though it does have items selected. So I'm not sure if it's something I'm doing wrong or what.

Thanks!

 


Comment by: helen (12/15/2017 10:49:22 AM)

Hello,

I am trying to display an item that when clicked in my slicer it display it in another cell.

I want to be able to do an if statement that so if certain things are selected then in another box then a message will display.

thanks for your help.
Helen

 


Comment by: Jan Karel Pieterse (12/15/2017 1:52:56 PM)

Hi Helen,

Can you elaborate? This page already shows how to get the selected items from a slicer?

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].