0

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?

2
  • Where did you input the field? could you provide a simple sample?
    – Lee
    Sep 11, 2019 at 8:56
  • @Lee I'd like the input fields outside of the pivot table. I don't want the customer to have to make changes to the pivot table.
    – sonic99
    Sep 11, 2019 at 11:58

1 Answer 1

0

Try this:

  1. 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))
  2. Update a source range data of your pivot table and add the new column.
  3. Set column "Filter_check" as a Filter
  4. In the filter set TRUE value
  5. 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

6
  • 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?
    – sonic99
    Sep 11, 2019 at 11:54
  • Hi @sonic99, I have updated the comment. You can try it. Best regards, Lukas
    – Muji
    Sep 11, 2019 at 13:22
  • Wow! That is so clever! I will try and see how it goes. Thank you @Muji
    – sonic99
    Sep 12, 2019 at 13:31
  • Hi @sonic99, any news?
    – Muji
    Sep 16, 2019 at 7:58
  • That worked! Thank you, @Muji I just need to get the Macro to work now :)
    – sonic99
    Sep 19, 2019 at 12:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .