PivotTable management

[gc6294]

Just looking for ideas.. I have a series of pivottables used for sales analysis. The reports are created via a crystal report reporting off of a SQL database. The Crystal Report is exported to a pivottable data file, then linked to the Pivottable Excel sheet. The problem is the Pivottable Excel sheet has turned into about 10 tabs, each one consisting of 2 or 3 different formats of the Pivottable. Each time the structure of the pivottable Excel sheet changes or the data excel sheet changes, I have to go into each of the 20-25 separate Pivottables, go into PivotTable Wizard, redefine the link and the range of the data excel file and then refresh the pivottable. Is there a more manageable way of doing this? Thanks...

 

[SkipVought]

Hi,

I am a big advocate of using ONE chart, ONE pivot table, ONE query table, ONE of any formatted report for consumption, and varying it via Controls on a sheet, assuming that ALL the reports use the same display formats. So if you have 2 ro 3 different formats, you could end up with 2 or 3 PTs.

The reason for ONLY ONE? It's exactly what you are experiencing -- a maintenance nightmare.

I would explore eliminating the Crystal Reports interim step, because you could directly query your SQL Database from Excel, using Data>Get External Data>New Database Query...

If you go this route, it will take a bit of VBA code and SQL code, so please repost in Forum707.

Skip,

_[red][/red]