I'm building monthly reports from one or more pivot tables (Excel 2003). My pivot tables have 3 initial columns: Trade Sector, Category, Brand (and then a load of data in columns to the right). The Trade Sectors obviously only appear once in the 1st column, but the Categories and Brands are repeated down the Pivot Table (e.g. all 10 Categories get repeated for each Trade Sector).
I'm building some neatly formatted tables that reference the pivot tables. The problem is that the number of rows in the pivot table might change each month as new data is fed into the pivot table (from a dynamic named range feeding off a big data dump that is automatically refreshed each month).
So I can't reference say cell $C$350 each month and expect it to be the first named brand in the third category, as the categories above may have expanded or contracted.
I can't use the GETPIVOTDATA command, as my brands are ranked from largest to smallest in each category so BrandA might be the biggest brand in the category that month, but it may be replaced by BrandC the next month.
What I need, as an example, is a way to be able to get Excel to look down column A and find TradeSector2 (which I can specify), then look down column B and find Category 4 (which again I can specify), and then return the 1st, 2nd, 3rd brands etc (which I can't specify) from column C for that category/trade sector combination.
Any ideas? I've tried both GETPIVOTDATA and INDEX/MATCH formulas, but can't find a practical solution.
Thanks for any help,
Tim.
I'm building some neatly formatted tables that reference the pivot tables. The problem is that the number of rows in the pivot table might change each month as new data is fed into the pivot table (from a dynamic named range feeding off a big data dump that is automatically refreshed each month).
So I can't reference say cell $C$350 each month and expect it to be the first named brand in the third category, as the categories above may have expanded or contracted.
I can't use the GETPIVOTDATA command, as my brands are ranked from largest to smallest in each category so BrandA might be the biggest brand in the category that month, but it may be replaced by BrandC the next month.
What I need, as an example, is a way to be able to get Excel to look down column A and find TradeSector2 (which I can specify), then look down column B and find Category 4 (which again I can specify), and then return the 1st, 2nd, 3rd brands etc (which I can't specify) from column C for that category/trade sector combination.
Any ideas? I've tried both GETPIVOTDATA and INDEX/MATCH formulas, but can't find a practical solution.
Thanks for any help,
Tim.
