YES it's very much possible that you can use a Cell Reference to Filter records while using the Pivot Table.
let me discuss one simple example. Suppose you have database in Range A2 to D20. Contains Headers for example Date, City, Name & Sales. Now to work with Cell reference to filter, steps shown below should be followed.
- Create a New Column
E
.
- write a formula like
=and(D2<=1000,D2>=5000)
, press Enter.
- Drag down the formula till end,(You find True & False as per Data).
- While building the Pivot table, move New Field(Column) to
Report Filter
OR
to Slicer
(if you have Excel 2010 or higher version).
- Choose
True/False
from the Filter.
- Go to Pivot-table Tools Options & hit Refresh.
You find Excel has included the New Field's effect and as per it shows the result.
NB: Remember in Place of TRUE you can use FALSE option also, you can use any possible method (Formula is better way) which can help Excel to Filter record.
Hope this help you. In case my solution differs, just put a comment that where you need correction.
Adjust cell references in the formula as needed.
If possible, I would prefer non-VBA solutions, but if it is the only way then please feel free to share. – macsmith – 2017-11-24T18:33:58.863