When the value in a cell changes change the filter on a pivot table
The problem is that C3 is not actually changing, because it's just formula reference that is updating. Is the "drop down selector" on a form or based on data validation and in a cell?
If it's based on a cell, set your target to be the target cell, not C3 -> because C3 is just a formula reference, and your drop down cell is the one actually changing.
If it's based on a form, but code in the on_change event of the form control.
asjohnson
I am a 20 something Minnesota based programming enthusiast. My educational background is in mathematics, economcs and statistics, but I have always enjoyed programming and since entering the work world I have realized that you can get a lot of value from programming. I am pretty handy with R and VB.NET is my default "time to program" language. I know some C# and a few other things (reading Learn You A Haskell and doing Codeacademy), but really my programming approach is very stack overflow reliant, so I am never sure exactly how much I know vs. how much I am able to look up online in a reasonable time frame.
Updated on June 04, 2022Comments
-
asjohnson almost 2 years
I have been reading around for a bit and reached the ask SO point. I have a drop down selector in excel that lets me change names and when the name is changed, the value in "C3" is changed (c3 references another cell on another sheet if that matters) and when the value in "C3" changes I want a pivot table on another sheet (sheet6 for now) to update its filter on territory id to reflect the new value of "C3".
It sounds like an event to me, so I went digging around and found out VBA has events (yay!), but I have been unable to get my event to actually do anything. The code I have there works when I run it as a macro, but I would really like it to automatically run everytime the value in cell "C3" changes.
Here is what I am trying so far:
Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Sheets("Current Status").Range("C3")) Is Nothing Then Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id"). _ ClearAllFilters Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id").CurrentPage _ = Sheets("Current Status").Range("C3").Value End If End Sub
Update: I have found that if I put the above code in the sheet section (sheet 2) instead of in a new module I can get it to run if I physically enter the code and then hit enter. Now I am wondering if there is a way to make it do it without me having to manually enter the value and hit enter. I just want to be able to use my drop down menu to select a new name and when the value in c3 changes due to the drop down selector update the pivot table.
Thank you as always SO.
-
asjohnson about 12 yearsThe drop down selector in based on data validation and in a cell. Could I point my event at the drop down selector? I guess I am still not hitting enter, so it might not work. Trying it now.
-
asjohnson about 12 yearsAnd it works! Thanks for pointing me in the right direction. I just had to change the reference to the drop down selector and everything is peachy.