Hi,

Working in Excel 2003, I have a Pivot Table containing an MS Query linked to an external csv file. There is a front end sheet containing forms designed to control the Pivot Table (with a raft of VB which takes the Pivot Table results and displays it in a different sheet, in a nice format).

There are times when I want to have more than one page field filtering the Pivot Table, but the limitation I've found is that these fields filter together with a logical "AND" operation - so it filters results where Field1 = 1 AND Field2 = 1. What I'd like to do is filter the table where Field1 = 1 OR Field2 = 1.

My first question is, is this possible to do within the Pivot Table options? (I've looked but can't find anything.)

My next question, given that I've abandoned all hope that the answer to the first question is yes, is can anyone think of a clever way of achieving the same result?

I want to avoid filtering in the row and column fields as I don't really like the look of multiple row and column fields. (And these will already contain other fields anyway, and it will mean the VB that reformats the Pivot results will need a lot of re-working.)

The ideas I have had so far a
i) Use VB to send an updated SQL query to MS Query behind the Pivot Table - the disadvantage I see to this is that the csv file needs to be available to the person using the document when the query refreshes, which isn't always going to be the case.
ii) I re-work the data behind the MS Query to calculate the values for all the combinations of fields outcomes - this will be massive as there are around 30 fields that could be filtered on, making the document big and slow... not ideal.

Google has been unable to help in this instance. Any pointers are welcome! Thanks in advance!

The solution I've come to is using a second pivot table using the same MS Query (just copied the sheet containing the original pivot table).

I then have the second pivot table filtered to show the opposite of what I want (i.e. where Field1 = 0 and Field2 = 0). Leaving the first pivot table unfiltered, I subtract the second pivot from the first pivot to arrive at a derived Field1 = 1 or Field2 = 1 filter.

There are little things to be careful of (like setting the option to display field values that do not contain data), but it does the job.