Report Filter Conditional

Hello!

I have a pivot table for which I currently have two report filters.
For ReportFilter1, I have football teams.
For ReportFilter2, I have the players that are in the league.

The thought process behind the pivot table, was that if I filter to a football team then the next filter should be conditional and only show me the players that are on the team selected. However, my ReportFilter2 is showing me ALL players that are in the league and not just the players that are associated with the team that is selected in ReportFilter1.

My question, is there a way to make ReportFilter2 ONLY show the values that are associated with ReportFilter1? Any ideas?

Review this site. It may help in your project.
http://blogs.msdn.com/b/gabhan_berry...vottables.aspx

I have uploaded an example workbook.

About this workbook and what I need it to do:
Don't get caught up about the data. This workbook was made ONLY for the purpose of trying to get the Pivot Table filters to work properly by typing in the value to be filtered by the first filter and then typing in the value to be filtered by the second filter. Code is provided in the workbook in the VBE window in its own module. I got the code from the above forum link (I just don't know what text to change in the code to match up to my workbook). I have named the two cell ranges that you type in as:

1st Filter: LeagueFilterRange
2nd Filter: TeamFilterRange

Please let me know if you need any additional information for what I am looking for. Need ASAP! Thank you

Hey Ollie,

So I did find a forum about Slicers, however they aren't compatable with 2007 or 2003. I have users who will be utilizing this report who will have 2003.

I opened your workbook you posted and I changed the example names to their respective names, however I was getting a run-time error '1004': Method 'PivotTables' ob object '_Worksheet' failed at line 10 on UpdatePivotFieldFromRange procedure:

Set pt = Sheets.PivotTables("PivotTable1") --> So I changed this to Sheets("Pivot Table").PivotTables("PivotTable1")

I stepped through and the code failed at line 26:

pt.RefreshTable

However, now the code isn't even running when I try to change the filter for the pivot table. I have re-submitted the example workbook that i am referencing. Any thoughts?

Ollie,

I opened your workbook that you provided and still get a run-time error at line 10 on UpdatePivotFieldFromRange:
Set pt = Sheet.PivotTables(PivotTableName)

Could it be a reference issue that I maybe don't have checked?

References I have checked are:
* Visual Basic For Applications
* Microsoft Excel 12.0 Object Library
* OLE Automation
* Microsoft Office 12.0 Object Library

Ollie,

No luck...

Steps I took:
1) Opened your workbook provided
2) Activated the LeagueRangeFilter cell and typed in "NFL" -- Enter

Received Run-time Error '1004': Cannot open PivotTable source file 'C:\Documents and Settings\user\Local Settings\Temorary Internet Files\Content.IE5\TSAMCDWR\[Football_pivot_Example%20OllieB%20'

This errors out at line 23: pt.RefreshTable

Also...

After I received the run-time error, I figured I would just re-save the workbook as "Football" and change
Set pt = ThisWorkbook.Worksheets("Pivot Table").PivotTables(PivotTableName) --> to -->
Set pt = Workbooks("Football").Worksheets("Pivot Table").PivotTables(PivotTableName)

However, the code won't allow me to run or even step into the code directly after the run-time error of the pathway.

Hey Ollie,

You were right, your solution in post #16 was correct. I saved to a directory first, then opened Excel then opened the file and I had no run-time errors. It works well when i do this. Is there a workaround for this? The reason I ask is this report will be posted to a location on the internet that users can go to and download the file. I can't have the users follow these steps everytime b/c they would think it was ridiculous...to me it's not a big deal, but the report is meaningless if I can't get that to work..ideas?

Initially I thought if I could have the users type something into a cell and the pivot table would update automatically, then I would just add some data validation boxes and apply them to the named cell, then I could have dependent data validation boxes. See where I am going with this? Any ideas for the workaround mentioned above?

Hey Ollie,

When I open the workbook, the file name gets changed. Where ever there is a space in the file name, gets replaced with a "%20". Also, at the end of the file name is [1]. So when I open the file, it reads: "Football_pivot_Example%20OllieB%20v3[1].xls"

When I opened v3, right away your Workbook_Open procedure failed at line 24:
pvt_obj_PivotTable.SourceData = "Data!R1C1:" & .Cells(.Rows.Count, "A").End(xlUp).Row & "C" & .Cells(1, .Columns.Count).End(xlToLeft).Column

and I received the following: Run-Time error '1004': Reference is not valid.

It is still erroring out at the same line in your v3.1

No worries Ollie!

You helped out a ton! I am extremely thankful for your help! If/when I find out a solution, you'll be the first to get a PM! haha

Again, you have been awesome!

Wow, well I have to say that is dissapointing to hear that it is working on IE9! haha. I think it will be okay...I will have to instruct the users to download and save the workbook to a directory first, then to open excel by itself, then open the file from the directory. As long as they do that, I am hoping it will work in 2003and 2007 versions (those are the only versions we use at my company). I am in the process of transferring your code to my workbook and adding data validation boxes. I've already added the first data validation box and it does work. I am sure I will run into some issues along the way with the transfer as well as I will probably need some enhancements to the code for error checking.

I am going to be working on this diligently, and will need something concrete by Friday. Are you okay with keeping this open over the next few days?

Hey Ollie,

I have been trying to log-in the entire day...ExcelForum has been done or something wrong. Anyhow, I finally finished the product and it's working lovely. However...I tested it with Excel 2003 and it failed miserably. It wasn't even recognizing the PivotTable name. Do you know of a workaround for so the code would be compatible with both versions 2007 & 2003?

Hey Ollie,

I have to create another pivot table that has the exact same functionality as the one currently built, just a different view of data.
I have tried doing on my own, but no luck. Did I follow the right steps?

In my "ThisWorkbook", I have added some more code to be the following since I now have two pivots on two seperate sheets:

Please Login or Register  to view this content.

As you can see, I have just simply added "2" to the end of each name and have added these name references to the workbook.
You probably noticed the PivotTable# names and indeed they are correct and do reference the proper PivotTables.


In the module "Workbook_SheetChange", I haven't changed anything.

The code is erroring out at the first line: If Not Intersect(Target, Application.Range("PortfolioFilterRange")) Is Nothing Then
and I receive a Run-Time error '1004': Method 'Intersect' of object '_Global' failed

Since there are two sheets, how can we get the code to recognize which sheet is activated so it knows which code to call when there is a change event on that specific sheet?


PivotTable2 located on Sheets("Analysis")
PivotTable1 located on Sheets("PM")

Hey Ollie,

Never mind I got it to work

I ended up putting this in the code:

If ActiveSheet.Name = "Analysis" Then

and it works well now. So never mind However, I might post something again soon haha

Hey Ollie,

I need to change the workbook change event to only be a worksheet change event. So only when there is a change on worksheet "Analysis" should the pivot table be refreshed. Where do I put this code and what do I need to change the following code to for it to only recognize when there is a change event on worksheet "Analysis". The following code is in the ThisWorkbook module.

Please Login or Register  to view this content.

Hey Ollie,

Thanks for the response! I would rather move the code to the worksheet_change event. When I take the same code that was in my "ThisWorkbook" module, and move to Sheet4(Analysis) module, I get the following error:

"Compile Error: Procedure declaration does not match description of even or procedure having the same name"

Steps taken:
1) Copied code from "ThisWorkbook", transfered to Sheet4(Analysis).
2) Removed existing code from "ThisWorkbook" (since code has been transfered to Sheet4)
3) Changed procedure name from Workbook_SheetChange to Worksheet_Change
4) Tried changing a cell on "Analysis" tab to trigger code ----> Compile Error

What did I do wrong?

Got it! I took out:

ByVal Sh As Object

Hey Ollie,
I have a curve ball for you...
Currently, the Pivot Table is filterable through the Data Validation lists and works extremely well. However, I now have an enhancement request which HAS to be done… and Data Validation will not work properly so I need to use a combo box.

So, I need to change data validation lists to a Combo Box instead:
• Every time I select a value from the combo box, there is a value that is changed on the “Analysis” sheet, and is cell F4 (I have named F4 “OBSFilterRange”). However, F4 contains a vlookup formula. Since the cell contains a formula and the cell itself isn’t changing (formula stays the same), the pivot table isn’t refreshing. Is there a way to change the following script to recognize when the cell value changes even though there is a formula that evaluates what should be in the cell?

Please Login or Register  to view this content.

Hey Ollie,

It's been awhile! I am trying to edit this VBA to filter the Pivot Table by "Begins With" the value that I am selecting.

For example:
If Cell F4 Value = Brian

Then the Pivot Table should filter all values that begin with the text string "Brian". So if the text strings contained in the data source were:
Brian, Brianna, Briana, Briant. Then the pivot table would show all of these values as they all begin with the text string "Brian".

Is this possible to edit the code below to filter by begins with text string instead of exact text string?

Please Login or Register  to view this content.