Filter Pivot Chart based on Cell Value

[alisachsf]

Hi,
I have a pivot table I would like to automatically filter on a specific cell value.
I have an employee ID in the filter section, and in a separate cell i have the ID (pulling from a different tab).
I want the pivot table to refresh and filter on that ID every time it changes.
Is that possible to do?

Thank you!

[Bernie Deitrick]

An easy approach is to use a column of formulas that compare the ID to the desired ID, like

=A2=Sheet1!$A$1

where column A has the IDs, and the ID of interest is in cell A1 of Sheet1.

Then just use TRUE as your page filter for that column of formulas.

[alisachsf]

Thanks Bernie, but I need to automatically filter the pivot based on that cell.
I'm assuming I need a macro for that.
I also need it to refresh every time the other cell changes and reapply the filter

[Bernie Deitrick]

If you have that field in your pivot table, a recalc and a data refresh will update the table. You could use the worksheet calculate event to refresh the PT if you don't want to manually refresh the data:

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed

Please Login or Register  to view this content.

[alisachsf]

Hi Bernie,
I understand the idea of filtering based on true, but what code would you use to do the filtering without touching it?
I want to change the other cell and get the pivot to refresh
I tried the following code:

Please Login or Register  to view this content.

That should've filtered the 6 column (F) based on cell D21 on the Dashboard sheet
But i get an error with this code
It worked for tables, but not for pivot or the added columns to the pivot (column F was not part of the pivot, pivot is columns A-E)
Any suggestions?

[alisachsf]

Just solved it! I changed the range to the added column only

Thanks!