Drilldown in Pivot Tables

Posted by Sonali on February 15, 2002 12:26 AM

I've created a pivot table and have enabled drill down on it. However, on drill down - i.e. double-clicking a row on the pivot table, it creates a new sheet. I want to be able to go back to the original sheet. Is this possible without writing a macro?

Thanks
Sonali

Posted by DK on February 15, 2002 2:07 AM

Hi Sonali,

You wouldn't be able to do this without writing a macro. If you just want to return to the data source when the user double clicks then there is no problem. Try this:-

Disable drill down, then right click your worksheet tab and choose View Code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Data source").Activate
End Sub

That would take you to your data source. If you want to return to the actual records then it would be far more elaborate. You'd have to determine the row and column header where the user has double clicked and then search the data source for the correct records. Unless you only have a few pivot fields then this could be a difficult task.

Regards,
D

Posted by Mark W. on February 15, 2002 7:48 AM

No, if I understand your intentions...

Drilldown will not highlight the data in the
original source data list; however, you could use
the separate list created by drilldown as a
critiera and filter the original data source with
an Advanced AutoFilter.