I know that I can go to Row Label filter –> Value Filters –> Greater Than etc. However, I'd like to have two input fields, From and To and I'd like for the pivot table to only display values within this range. Is this possible?
1 Answer
Try this:
- Create a new column (e.g. Filter_check) in a source data with formula comparing filter fields "From" and "To" with a data field in the source data range with output format True/False (e.g.
=if({data field}>={Field from}, if({data field}<={Field to}, TRUE, FALSE), FALSE)
) - Update a source range data of your pivot table and add the new column.
- Set column "Filter_check" as a Filter
- In the filter set TRUE value
- Next create a macro for updating a worksheet after every change
MACRO:
Private Sub Worksheet_Change(ByVal Target As Range)
application.EnableEvents=False
ActiveWorkbook.RefreshAll
application.EnableEvents=True
End Sub
When you change values in fields "From" and "To" it should work.
Best regards, Lukas
-
Thank you @Muji I'm wanting to have input cells for to and from in the dashboard. I'd like the pivot to pull in the values from these input fields, is this possible?– sonic99Sep 11, 2019 at 11:54
-
Hi @sonic99, I have updated the comment. You can try it. Best regards, Lukas– MujiSep 11, 2019 at 13:22
-
Wow! That is so clever! I will try and see how it goes. Thank you @Muji– sonic99Sep 12, 2019 at 13:31
-
-
That worked! Thank you, @Muji I just need to get the Macro to work now :)– sonic99Sep 19, 2019 at 12:47