Applying filter to original data from pivot table

Hello all! Instead of Excel creating a new sheet when I double click on the results of a pivot table, is it possible to just filter the results of the original source data's sheet?

Here is my situation, I have source data that needs to be updated after someone double clicks on the pivot table data. They won't be able to update the source data if it just creates a new sheet (since it's a copy).

I'm running Excel 2003. Thank you in advance!!!

Wayne

Rob, thanks for responding! Attached is an example of what I'm looking for. The Master-Before is the data prior to any filtering.
The Master-After is how I would like the data to be filtered.
The Pivot sheet is the pivot table and the example is if I double clicked on the total column for Terry in January (C7).

I just need to double click on the Total column in the pivot table and for the original Master data to be filtered (and possibly activated) so I can make changes to the original data with the entire row displayed (and no new sheet created), but only have those filtered lines showing.

Hope that makes sense. If you have any old code, that would be great!!! Just found this forum and it has tons of tips and tricks in it. Thanks!

Wayne

Rob, I did find some code to do what I would like here: http://www.contextures.com/xlPivot-F...urce-Data.html

Unfortunately, I get a 'Subscript Out of Range' error (Run Time Error 9) when I went to go test it out (on their sample file). Do you get the same? I'm running Excel 2003.

My sample file is not the same format, but I could delete a bunch of source data, headers, etc. and upload. Thx!

This is the line of code it errors out on me with:

If Application.CountIf(Worksheets(xSht).Range(xRng), xCell) > 0 Then

I'll have to send up an actual example of the worksheet minus real data. I'll see if I can upload tomorrow. Thx!

Rob, example with true field layout for source and pivot table is attached. I would like to click on any of the totals in column H on the pivot table and have it filter the source data on the 'MasterData' sheet. Thanks!!!!

Wayne

I'm working on a spreadsheet that does something similar at the moment, difference is my pivot data needs to be integrated with non-pivot data (i.e. pivot data, manual entry, pivot data, manual entry and so on) so I'm using GETPIVOTDATA extensively to maniupulate the display

Macro 1: determines the values to filter on

Marco 2: takes values and filters the data on those values

I can get this code to you on monday when I'm not on a mac (who on earth decided not to include vba on excel for Mac!?!)

this is what I'm using for the actual filter bit:

Please Login or Register  to view this content.

where row 1 is the header row of your data

Zonino, if you could please forward the macros, that would be great! I'm not an Excel coding pro and any help would be appreciated! Thx!

Rob, not a problem! I think both solutions would be great! If you can figure out the problem, please share!! Thanks!

Rob/Zonino, make any progress on the code or macros?

Rob, could the error be because of a referenced DLL or library that is not included?

Zonino, I know what you are trying to do, but can't figure out how to make it dynamic to where it could be double clicked on the total and have it figure out the filter on another sheet.

Thanks guys! I know I'm hardly a priority.

Wayne

Figured it out! That scripting sample will not work if it is pulled from a .zip file. You need to save it to hard disk. Also, I made a change to the script to where it will properly filter (blank) pivot fields (otherwise it uses "(blank)" as a filter)...and nothing appears.

Hi Wayne, sorry I keep coming on the forum when I'm at home on my Mac! will try and remember to post this tomorrow at work