I have a simple range of rows and columns. The first column is department and another column is location. The rows consist of descriptions of projects taking place within a department at different locations. I have used the subtotal command which enables the expansion of each department listing their projects. The subtotal rows count the number of projects and various other metrics in other columns. Then, of course, there is a grand total row at the bottom. So far, so good, the perfect solution I was looking for. However, now I simply want to filter the location column so I just show projects at a single location. This works fine when the table is fully expanded. However, when I compress the range to just the subtotal rows and then expand a particular department or the whole table, it ignores the filtering and shows all the locations again, even though that column is filtered. Is this a bug? I've search for hours on the web to see if anyone has had a similar problem but have not found a mention. I don't see another simple solution to this. Pivot tables won't work because I don't want to summarize all the data, I want to show the rows and subtotals for those rows filtered one way or the other. Seems like this should be easy. The only other solution I can envision is to create my own table and use macros to do the same thing the subtotal command does, but have it work properly which would be a lot of work. Or create a separate tables for each location. Any thoughts?
