I'm new to forums and haven't posted anything before.
I have created a pivot table from a large range of data which includes Region, Customer Name, P&L Level, Products and then the total amounts for 2015, 2016 & 2017. From this I need to do more analysis, so I have used the Name Manager to create named ranges for the Customer for each Region and amounts by Region for each of the years. This means that I have 4 named ranges for each Region. The formulas work when I have the full data showing in the pivot, but when I filter on one of the P&L levels, the Regions inevitably shorten. I have worked around this by redefining the ranges as to what is now displayed, but this then needs be repeated for each P&L level, making this a very manual process and open to errors. The usual amount of data I am working with consists of over 255k lines and is ordered by date. This needs to stay ordered in this way, as there are other formulas within this data which won't work if the data is rearranged. The pivot table usually covers over 100k lines of data.
The attached shows a file of test data similar to the data I am using. The first pivot shows the named ranges and the second pivot shows the named ranges when the pivot is filtered.
Is there any way to make the named ranges dynamic and so change whenever the pivot is filtered in any way? I am using Excel 10 but also have Excel 13.
Bookmarks