I record my expenses in an Excel spreadsheet. In a second sheet I have a pivot table that allows me to group my expenses by month and by category to see the totals. If I double click on a cell, a new sheet is automatically added that displays a list of expenses for the month/category selected. That's pretty great, except that the new sheet contains a copy of the expenses, so I can't update them. Also, I have to keep deleting these sheets each time I drill down, which is pretty annoying.
I found one example that explains how to automatically rename and remove the added sheets here: http://www.contextures.com/excel-pivot-table-drilldown.html
What I would really like is to switch back to the first sheet and update the filters accordingly. Does anyone know how I might achieve that?
Many thanks,
Patrick
Best Answer
Not extremely straightforward. I've rebuilt code from a daily dose of excel to take advantage of the better filtering options of excel 2010. If you select a data point in your pivot and run the macro, it will give you the matching lines in your source data. It does so by using the Show Details function, then creating a filter for each column to match the data.
You can set it on a new right-click button, or overwrite the default show details behavior.