Is this pivot based on another pivot? 1

[AccessHelp123]

Hi,

I have a worksheet which has 10 pivot tables. Five of the pivot tables as sourced through an external data source (Access). The rest of the pivots are based on the first 5 pivot tables. The problem is at this point I dont know which pivots are based on the other pivots and which ones are externally sourced. Does anyone know of a property in Excel that shows this information. Thanks.

 

[AnotherHiggins]

This isn't a VBA solution, but it doesn't really seem like you need one....

In Excel 2003 and earlier:
anywhere in a Pivot Table, Right Click > Pivot Table Wizard > Back will show you the source data.

In Excel 2007:
select any cell in a Pivot Table, Pivot Table Tools > Options > Change Data Source



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AccessHelp123]

John.. thanks for you reply. I tried right clicking on all pivots > Pivot Tbale Wizard > Back. But they all have external data source radio button checked. So I am not sure how to tell which one is based on a pivot (but I know initially when the pivots were created that is how they were created).

 

[Gavona]

Well I know an indirect way (Excel 2003).

Right click on the table and choose Table Options

change the radar button for save data with table layout.
I believe that this changes the setting for all pivots that share the same pivotcache.


Which gets me to thinking - explore the PivotCaches collection?



Gavin

 

[combo]

All tables point to external data. You can test SourceData property of pivot table, or, after getting pivot cache (PivotCache method) its SourceData or SourceType.

combo

 

[AccessHelp123]

When I look at the pivotcache collection and see what the source is using VBA it just points me to the SQL database that the pivot table is pointing to,not the pivot table it is being based on. Thanks for your help.