May 09 2020 02:00 PM
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?
May 09 2020 02:18 PM
the best way is to convert your range into table then go to the table option and select total row, it will not effect the filter as well, try it and tell me what happen ?@tmoerke
May 09 2020 02:38 PM
@tmoerke Hi. I am loading this word document explaining step for step how I think you are trying to acheive your objective. It's totally different data, but it shows you how to get what I think is the result you are trying to acheive. Have a read and hope it helps.
Jun 07 2021 08:52 PM
I think you are missing our problem. When filtering, the subtotal, 9 does not change the sum to reflect what is being filtered (as would be expected).
Mar 01 2022 02:38 AM