by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see. It is a great user experience.
Here is a sample use case and demo
You are browsing and drilling into a product hierarchy looking at the change in performance vs last year. You drill down to the product level and want to see the weekly sales for that particular product. You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at. Let me share the process of how to build this interactive report.
Use the VBA Recorder to do the heavy lifting
After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code. Here is the process I followed.