Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

This question has generated more views than anything else I've posted here, so I thought I would come back and write up how I finally solved this situation. I never did get advanced filters to refresh automatically. Instead, I did the following:

  1. Create an additional column alongside the data to precalculate the more complicated parts of the criteria. This just needs to be a boolean and evaluate to TRUE or FALSE. 1a. If there is data in your table you need to handle in more than one way, or group in some fashion, create extra columns to handle the grouping. For example if you may need to filter by week or by month sometimes, and you already have a "date" column, add a "week" and a "month" column which are precalculated based on the "date" column.
  2. Create a pivot table. Put all the column headings you need in the "rows" section and nothing at all in the "columns" section of the pivot table design.
  3. If there is one of the columns that the user will routinely want filtered for only one value, add that column heading to the "page" section of the pivot table design to allow easy filtering.
  4. Refresh the pivot table before each use.

In my case, I set the pivot table to sort by student, then subject, then date, by simply putting these three column headers into the "rows" section of the pivot table design in that sequence. I created a "semester" column in the original data table which just displayed the semester according to the date entered in each row, and added "semester" to the pivot table design in the "page" section, to allow the user to filter for any one specific semester. I created another column in the original data entitled "FOR_GPA" with a complicated formula simply designed to determine, yes or no, whether the data in that row should be calculated into the student's GPA (e.g. yes for usual tests, no for electives, etc.) This worked beautifully and totally solved the "how to reapply advanced filter" problem as well as allowing for greater flexibility in how the data was presented and summarized.

If any part of the above is unclear please comment below and I will try to clarify.