1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using a scrollbar on a pivot table with filters

Discussion in 'Ask an Excel Question' started by Dominic Martin, Jul 17, 2017.

  1. Dominic Martin

    Dominic Martin New Member

    Messages:
    4
    Hi,

    I have a pivot table that basically looks like this:

    Category Description Status Filter ANOther
    Cat1 jfffmfmfmfm Stat1 No jhfkfkffghfhgf
    Cat1 wdfafhktfhmf Stat2 No ohgjkfghkfhgdfjh
    Cat1 ghdfjhdhkdk Stat1 Yes ghfhjgfghfghdf
    Cat2 hgjhgjhgjhgj Stat1 No hgfhgkffghfghf
    Cat2 gjfghghdfghdf Stat2 Yes ghdjhdfjhdjdj

    The layout is set to show all rows in tabular form.

    I'm putting the pivot table onto a dashboard that only has space for 22 rows and the full table is about 120+ rows. So I wanted to use a vertical scrollbar; hide all but 22 rows and change the visibility as the scrollbar was used.

    Thanks to the following post, I have the fundamentals of it working. In particular, Hui's Filter Pivot Sub.
    http://forum.chandoo.org/threads/us...lly-on-a-simple-pivot-table-not-offset.26342/

    I want to use Description as the .PivotFields.

    The result is that the items did not scroll smoothly and appear/disappear one at a time. It was seemingly random as to what would be visible and what would hide. So I changed the action from .PivotItems(i).Visible = True to .PivotItems(i).LabelRange.Interior.Color = vbYellow and did a debug.print .PivotItems(i) to see what happens to the scroll items.

    I think I have two problems.
    1. The pivot table is filtered by the Filter column to only show the No's. However, the Sub picks up all the Descriptions. So, it may only look like 16 items have been made visible but if you took off the filter, then there are 22 items selected.
    2. I think there's a problem with the sort order. The Pivot Table is sorted by Category for the listing but when selections are being made, they're not following the sort order that the user is seeing.
    So, I need to adapt the Sub to find a way of getting Excel to ignore any items that are filtered and to use the sort order that the user sees, so that it scrolls smoothly.

    I've tried using .VisibleItems instead of .PivotItems but the Description is always visible. It's just that some Descriptions have been filtered out using the Filtered column. Any suggestions?
  2. Dominic Martin

    Dominic Martin New Member

    Messages:
    4
    Just to clarify the pivot table layout a little better.

    Category | Description | Status | Filter Field | ANOther Field
    Cat1 | jfffmfmfmfm | Stat1 | No | jhfkfkffghfhgf
    Cat1 | wdfafhktfhmf | Stat2 | No | ohgjkfghkfhgdfjh
    Cat1 | ghdfjhdhkdk | Stat1 | Yes | ghfhjgfghfghdf
    Cat2 | hgjhgjhgjhgj | Stat1 | No | hgfhgkffghfghf
    Cat2 | gjfghghdfghdf | Stat2 | Yes | ghdjhdfjhdjdj
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,379
    Hi ,

    Can you upload your workbook with the data and code in it ?

    Narayan
  4. Dominic Martin

    Dominic Martin New Member

    Messages:
    4
    Hi Narayan,

    Attached is a sample of what I have at the moment.

    The aim is that the scrollbar should hide/unhide items from the pivot table so that only 12 (or 22 in my real worksheet) are visible at a time. To see what's going on, I substitute the Visible True/False property with a colour. So the first 12 in the list should turn yellow and the rest red. If you move the scrollbar down 1, the first entry turns red, followed by 12 yellow, then the rest are red and so on.

    The pivot table has two filters (which are required). These can be applied programmatically if it helps.

    1. You'll see if you take off the filters and look at the debug output that the 12 in yellow match the 12 in the Immediate Window. When you apply the filters, there is no longer 12 in yellow because some of those yellows have been filtered out.

    2. The visible pivot table is ordered by Category but the selection on the description is made alphabetically. So the 12 in yellow are not a single block. If you move Description as the first column, the order is the same and a contiguous block is selected. But that's not the way I want to lay out the pivot table.

    selecting Category as the field to work on doesn't work because it doesn't scroll 1 line at a time due to the repetition of Categories.

    UPDATE: Since the description is no longer unique in my sample, this isn't working like I described. If you change the PivotFields to use ID instead (which is unique), then it works as per the above.

    Thanks for helping.

    Attached Files:

    Last edited: Jul 18, 2017
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,379
    Hi ,

    I am not sure whether this is what you want.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  6. Dominic Martin

    Dominic Martin New Member

    Messages:
    4
    Thank you so much for your help.

    Do you know if it's still possible to just hide the items on the pivot table, rather than the whole row? The pivot table is part of a dashboard with other things to the side of it. So as rows are hidden, parts of the dashboard would disappear.

    For now I could just put the pivot table underneath everything else but the ideal would for it to be next to something and just hide the items on the pivot table.

    However, this has at least given me something that works, so thanks.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,379
    Hi ,

    In case you are still on the look out for an improvement , see the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page