Blogger
Share this content
0
6
16819

Drill down in Excel ?

Is there any way I can create a drill-down in Excel, so that I can click on a cell in a summary sheet and it takes me automatically to the detail elsewhere in the workbook? (I'm using Excel 2003).

Many thanks, in anticipation

Replies

Please login or register to join the discussion.

avatar
By stevie
03rd Nov 2011 10:40

Use pivot tables

Summarise the data in a pivot table. You can then double click a cell in the table and it will open a new worksheet with the detail.

 

Or maybe use hyperlinks.

Thanks (0)
avatar
03rd Nov 2011 11:52

yes it i, but

I have done this using VBA and DAO.  I used the right click and/or double click event to identify a drill down was requested, and then captured the KEY information and ran a parameterised SQL query on a detailed data table using DAO to return the required extract, which I dumped into an output sheet.

Apologies if it sounds convoluted - it works very well.  Without this then I would have thought either pivots or groups/outlining/subtotals would be the answer.

Thanks (0)
avatar
03rd Nov 2011 11:53

Or, a less sophisticated approach,

 

1) Go to Excel Options > Advanced

2) Uncheck the "Allow editing directly in cells" box - it's in the "Editing Options" part of the screen

Now, if you double click into a linked cell, it should jump to the source, whether it's on the same sheet or a different tab. The only drawback is you lose some functionality in the keyboard editing on the cell but you get used to it - I find it very useful.

 

(this is on Excel 2007 so things may be in a slightly difference place on 2003):

Thanks (0)
avatar
03rd Nov 2011 12:19

Use a hyperlink

Insert hyperlink.  You can set this up to take you to a cell in the spreadsheet, or to anywhere else in cyberspace.  It will change the cell formatting to change the colour and underline the text, but you can change it back to the default format afterwards.  I use this for contents pages when I have multiple sheets.

Thanks (0)
avatar
03rd Nov 2011 14:38

I use hyperlink

and add a 'Back' link in the target so that you can flip backl and forth.  If you target the sheet, rather than a specific cell, in the latter it should take you back to where you came from.

Thanks (0)
By shurst
04th Nov 2011 11:00

Keyboard shortcut for precedents

It's worth noting that if you don't want to turn off "Allow editing directly in cells", then the keyboard shortcut Control+[ (left square bracket) will go to the first precedent cell reference.

Thanks (0)