Thank you for your reply.Perhaps using getpivotdata function? See attached.
Couple of things:
- do not add dimensions to the pivot. It will break the formula.
- ensure labels used in the table do reflect the labels form the pivot.
How this is done? Simply refer to a pivot cell that holds measures. Then replace the hard coded labels with cell references. Wrapped in IFERROR to get rid of error values when dimensions are not present in the pivot table.
Hi, when I change the country in the filters, the pivot is updated and thus ... also the formulae I used. Not sure I see where the problem is.Thank you for your reply.
It seems you have linked the formulas with the pivot table as when I change the country to New Zealand. It is not coming up with proper output.
It would be better to update or link formulas based on the Raw data sheet according to the pivot table filter criteria.
This seems to work fine.
Yes you are right..they are not linked to pivots. But the numbers are picked up from pivots. I had created 4 different tables for the same(In the filter option country should be AUS and category should be Payroll management and the same for NZ as well. As of now I am doing it manually. so I am trying to automate that part. It would be of great help if you suggest some.Sounds like those extra charts are not made as pivot charts then? Without the actually workbook, that's rather hard for me to tell.