+ Reply to Thread
Results 1 to 7 of 7

Filtering a pivot table based on the value of a cell

  1. #1
    Registered User
    Join Date
    03-14-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    7

    Filtering a pivot table based on the value of a cell

    Hi everyone,

    I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

    The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

    Thanks for your time!
    Attached Files Attached Files
    Last edited by fbonetti; 04-12-2011 at 04:19 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Filtering a pivot table based on the value of a cell

    Hi fibonetti.

    I've changed/fixed the code. See if this does it for you. See if you can find what two lines of code were added. I also made a Validation dropdown for the country choice. Click on the blank for all.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-14-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Filtering a pivot table based on the value of a cell

    I knew it would be an easy fix :]

    Thanks for the help!

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Filtering a pivot table based on the value of a cell

    I'm not sure it was that easy. I have concerns about most of the code being behind a ThisWorkbook event.

    It is fun to read other peoples code from time to time but I have no idea what was going on here. I wondered why you didn't simply have a Filter for the region like normal pivot table filters. It would be the same thing except not need code to work it.

  5. #5
    Registered User
    Join Date
    01-29-2013
    Location
    Leiden, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Filtering a pivot table based on the value of a cell

    Hi, I am new on this forum.
    Looking for something similar, because I need to refresh a number of graphs whereby I need to filter on a weeknbr. I am looking for the possibility to enter a cell nbr in the macro, sothat it will automatically refresh all the graphs to the weeknbr. entered in that particular cell.

  6. #6
    Registered User
    Join Date
    03-24-2015
    Location
    Paris, France
    MS-Off Ver
    2010
    Posts
    1

    Re: Filtering a pivot table based on the value of a cell

    Hi I have used this code in order to refresh both of my pivot table from a drop down cell in C2 and it works perfectly. t

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$C$2" Then Exit Sub
    Dim Sh As Worksheet, Pt As PivotTable
    For Each Sh In Worksheets
    For Each Pt In Sh.PivotTables
    With Pt.PivotFields("NASP_END")
    .ClearAllFilters
    .CurrentPage = Range("$C$2").Value


    End With
    Next Pt
    Next Sh

    End Sub

    The only issue is that I need to filter my pivots with other information set in C3 and C4 using the same process but I can't seem to make it work for multiple selections and filters ...

    Can someone please help me on that ?

  7. #7
    Registered User
    Join Date
    08-28-2015
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    1

    Re: Filtering a pivot table based on the value of a cell

    @MarvinP. Thank you for your efforts ... just what I was looking for ... but with one problem I note ... If I remove the Validation on the Filter Cell and then input a value into cell that is not part of the target column and then ENTER the pivot does not go to 'empty' but still lists values. It would be good if the filter cell value is not found to render the pivot table empty. Is this possible?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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