+ Reply to Thread
Results 1 to 9 of 9

Set pivot table value filter with cell value

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    Boston, MA
    MS-Off Ver
    2013
    Posts
    10

    Set pivot table value filter with cell value

    I am looking to use the number entered in a cell as the "greater than or equal to" value filter for my pivot table values "Order History" and "Percent Difference."

    I have two variables: Minimum Order History and Minimum Percent Difference (cells D3 & D4). I would like any entries that do not meet both pieces of criteria to disappear. I have zero VBA knowledge so hoping that someone will be able to assist me!

    The workbook is attached and any help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Set pivot table value filter with cell value

    You cannot have more than 1 value filter applied to any given PivotField.

    Workaround is to add one of the field to Report Filter.

    Also, I need bit of clarification.
    Minimum% Difference... you only want positive % Difference >=40%?

  3. #3
    Registered User
    Join Date
    01-11-2017
    Location
    Boston, MA
    MS-Off Ver
    2013
    Posts
    10

    Re: Set pivot table value filter with cell value

    Correct...only positive. I can apply a filter when exporting the data for the minimum order history so if it can only be done for percent difference, that is perfectly fine.

    Thanks!

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Set pivot table value filter with cell value

    Something like below for filtering Percent Difference.

    In Standard module.
    Please Login or Register  to view this content.
    Then in Worksheet module for Sheet3.
    Please Login or Register  to view this content.
    Sample attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-11-2017
    Location
    Boston, MA
    MS-Off Ver
    2013
    Posts
    10

    Re: Set pivot table value filter with cell value

    Thanks so much CK76! I am getting a bug with the following line:

    Private Sub Worksheet_Change(ByVal Target As Range)
    (1st line for sheet 3)

    I have no knowledge of VBA so I don't know what would be used to correct this. Do you have any idea what may be wrong here?

    Thanks!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Set pivot table value filter with cell value

    Did you paste in worksheet module?

    0.JPG

    On another thought made modification which would make it bit more dynamic. It assumes there's only 1 pivot table on the sheet and Field names are same as the sample.

    In worksheet module.
    Please Login or Register  to view this content.
    In Standard module.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-11-2017
    Location
    Boston, MA
    MS-Off Ver
    2013
    Posts
    10

    Re: Set pivot table value filter with cell value

    Thanks again! I followed your instructions but I am receiving the same error with the new code as well. The first screenshot is the initial popup and the second is after pressing OK.

    2017-03-09_16-20-10.png

    2017-03-09_16-21-41.png

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Set pivot table value filter with cell value

    Hmm? That error tells me that you either don't have Sub FilterPercentDiff in the right location or at all.

    Make sure that is copied to Module1.

  9. #9
    Registered User
    Join Date
    01-11-2017
    Location
    Boston, MA
    MS-Off Ver
    2013
    Posts
    10

    Re: Set pivot table value filter with cell value

    That was my issue. I had the code in ThisWorkbook rather than in Module1. Thanks so much for your help!!!

+ 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. Change Pivot Table Filter from Value in a Cell
    By andyboles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2016, 02:05 PM
  2. Use Cell Reference for Pivot table Filter
    By Miskondukt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2016, 11:22 AM
  3. Pivot Table to filter according to cell value
    By torrmel9701 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-08-2014, 04:15 PM
  4. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  5. Pivot table, assigning a cell wo be change the Pivot table filter
    By jwongsf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-28-2012, 05:00 PM
  6. Pivot table filter from cell value
    By bravebart in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-14-2012, 09:51 AM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 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