I'm trying to create 1 pivot table on separate worksheets, 1 per region.
The source data is in 1 worksheet.
I have added a page to the sheet that contains the pivot table.
Now I would like to filter the pivot table by region.
Is there a simple way to do this ?
Would have been nice if the Excel 'Show Filter Report Pages...' worked.
When I try to put the data in the same worksheet as the pivot table I get a 'Reference is not valid' error.
I don't think what you're trying to do is possible without marcos/scripting.
NPrinting can parse the data sets on different worksheets using the "Page" feature, but it's not possible to put the Excel pivot table on the same worksheet as the source data.
You could probably write a macro which would take that worksheet and the build pivot table(s) based on the discrete data sets on each worksheet in the report.
I can't think of any other way of doing this.
View solution in original post
Thanks for the reply Aran,
I was just hoping there was something I was missing.