+ Reply to Thread
Results 1 to 2 of 2

Remove Items from Pivot Table Filters or Remove Items from Slicers

  1. #1
    Registered User
    Join Date
    12-19-2020
    Location
    Turkey
    MS-Off Ver
    2019
    Posts
    17

    Remove Items from Pivot Table Filters or Remove Items from Slicers

    Hi guys,

    I have a pivot table that draws data from a column dynamically. I say dynamically because all the data that is drawn is being constantly updated by some formulas. The problem is when I draw data from these columns pivot table counts the empty cells (with formula but with use of a in function they are blank) as data. Hence I get a blank item on my slicers which I could not figure out how to remove. Please note that the blank item I get on the slicers are not the kind that can be removed from slicer settings.

    I am afraid I cannot upload the whole workbook as it is too large and since all sheets are linked to each other by formulas and VBA I cannot simplify it for a sample workbook.

    I hope I was able to explain my issue clearly.

    Thank you in advance!

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Remove Items from Pivot Table Filters or Remove Items from Slicers

    So, a simple solution would be to use a Dynamic Named Range as Pivot Source

    If we assume a setup where Data is in Cols A:? and headers are in row 1...

    Name: =_PTData
    RefersTo: =$A$1:INDEX($A:$IV,MATCH(9.99E+307,$A:$A),MATCH(REPT("Z",255),$1:$1))

    Then set the Pivot source range to be _PTData

    The key to the above approach, given your use of Null strings in precedent range, is picking a column for the first match that is always populated (if row valid) and which contains numerics

    If you don't have any columns that adhere to this rule you can either:

    a) modify the MATCH to find last non-null (a bit more expensive, computationally)
    b) modify your precedent formulae to return 0 rather than null string, (use custom formatting to mask the 0), and change first MATCH from 9.99E+307 to REPT("Z",255)

    Another more general option is to use a Table however, I've assumed you cannot do that here as you need to dynamically expand row via formulae as opposed to physical inserts

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to update a pivot table containing multiple filters (not multiple items in a filter)
    By phrankndonna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2020, 06:35 PM
  2. Create a table/pivot table using different items items from a column
    By stevetton in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2016, 10:30 PM
  3. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  4. Filter pivot items using pivot items from another table
    By DKolev in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2014, 08:49 PM
  5. Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  6. Pivot tables - remove duplicate items
    By cmb80 in forum Excel General
    Replies: 6
    Last Post: 05-09-2012, 05:34 AM
  7. Replies: 3
    Last Post: 03-16-2005, 01:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1