+ Reply to Thread
Results 1 to 3 of 3

Use A Custom List of Values To Filter Pivot Table

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    7

    Use A Custom List of Values To Filter Pivot Table

    I'm working on a pivot table where one of the columns has 24 possible answers; however, the answers actually used in the source varies. The problem is that my filter list only shows answers that have been used in the filter source (which changes frequently). Basically, to simplify, possible answers for the source could be 1, 2, 3, 4 - I want the corresponding pivot table to show only 3 & 4; however, upon update, only 1 and 2 was given and I can no have 3 and 4 on my filter list. I realize this creates an empty pivot table, but the idea is to have 3 & 4 pre-selected so that, if that answer should be there upon refresh, the table will show those entries, and if not, then the table is empty but the filter remains for use in the next update.

    I hope that makes sense. I have pictures of my pivot and its source attached. My pivot is currently filtered to show "Occupational Therapy" and "Physical Therapy". I want to be able to have the filter also selected for "Speech Therapy", "Music Therapy", etc., as these answers could populate and should be part of the schedule. However, I don't want the person viewing it to ever have to change the filters, just use a refresh macro that I put on the sheet.

    Help, please!

    Pivot.PNGSource.PNG

  2. #2
    Forum Contributor
    Join Date
    05-08-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    102

    Re: Use A Custom List of Values To Filter Pivot Table

    It may help forum members assist you, if you could post your spreadsheet so that we can see what it is you are trying to say in words.

  3. #3
    Registered User
    Join Date
    12-18-2012
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use A Custom List of Values To Filter Pivot Table

    OK, I found a solution. Not very elegant, but it works. I added all the possible solutions at the bottom of my pivot table source and left the column on its left, also part of the pivot source, blank. Since I didn't need blanks represented in the table, I was able to filter out the blanks from that column to hide my dummy answers. That gave me the option to include every possible answer in the first column's filter in case they were to show up in one of the relevant rows. This should work in just about any situation. If you need to represent blanks in your table, you could mark the dummy answers with some other impossible answer that you won't need represented in the pivot table. Hope that makes sense to anyone looking for 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. Filter A Pivot Table Multiple Times Based On Values In List In Another Sheet
    By w.m.christensen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2013, 03:55 AM
  2. i cannot filter the values from drop down list from a pivot table report
    By mamun_08023 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-21-2012, 02:50 PM
  3. Creating a Custom Pivot Table Report Filter
    By csivell in forum Excel General
    Replies: 0
    Last Post: 08-08-2012, 02:21 PM
  4. Custom order in Report Filter for Pivot table
    By papvan33 in forum Excel General
    Replies: 0
    Last Post: 04-20-2011, 04:50 AM
  5. Custom values in filter of pivot?
    By T4FF in forum Excel General
    Replies: 4
    Last Post: 07-20-2010, 11:35 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