Drilling down in excel

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Drilling down in excel

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi All:

    In the spreadsheet attached, what I want to do is setup a drilldown so that if someone clicks a cell with a value in it, it will show everything that makes up that number.
    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows ME
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    3
    Relay2201
    4
    PR1500
    5
    HW100
    6
    SW200
    7
    Consulting200
    8
    PR2678
    9
    TR1567
    10
    TR2456
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    So in the example attached. I want to setup the spreadsheet so that if someone double clicks cell B3, which is $2,201 then everything that makes it up...i.e cells B4, B8, B9 and B10 are shown otherwise hidden. If someone wants to double click B4 then I want cell cells A5 to B7 to show up.

    Can someone please help work through this.

    Thanks,

    BA

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have thought/seen Group and Outline ? this seems like a good solution for your problem
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Group and Outline is something I use all the time but it won't work in this case because I want the detail to show after someone double clicks the number.

    Is there a way to show the detail if someone double clicks the number.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not sure if you are asking how to get the drilldown itself to occur (and hence the drilldown information accessed), or how to write the code that produces your drilldown information.

    To offer a drilldown feature, maybe doubleclicking a cell is a good way to set it in motion. So, put a double click event in your sheet code, like this (I took column B because it's the one with numbers on it per your post):

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 2 And Target.Value <> "" Then

    'Your code goes here that speaks to the number value in the Target that was double clicked

    Cancel = True

    Else
    Cancel = True

    End If
    End Sub

    Does this help at all or did I guess wrong on what you want?

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom:

    I am looking to get an idea of the VBA code that would enable drilldown. When I tried your code though I got a compile error. Can you please show me how to write the code that would lets say do the following:

    If someone doubleclicks cell B3, then A4:B4, A8 to B10 will be shown. What I also want to do is keep these details hidden initially until someone actually double clicks the number.

    Thanks so much for your help.

    Bharat

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tested that code and it works fine. Did you put it in the sheet module?

    Try this modification to get you started. Right click on your sheet tab, left click on View Code, and paste this into the module.

    When you double click on B3 or B4, a message box will pop up that represents the location where you'd place whatever code you need to compose to produce the information about A4 and A8:A10 respectively.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Address
    Case "$B$3"
    MsgBox "Insert code here for cell A4."
    Cancel = True
    Case "$B$4"
    MsgBox "Insert code here for range A8:D10."
    Cancel = True
    Case Else
    Cancel = True
    End Select
    End Sub

  7. #7
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom:

    I inserted the code where it was indicated but it still isn't doing what I need it to.

    Can you please help me fix this code:
    rivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Address
    Case "$B$3"
    If Target.Address = "$B$3" Then
    If Target = "" Then
    bShowHide = False
    End If
    Selection.EntireRow(4).Hidden = False
    End If
    Cancel = True
    Case "$B$4"
    MsgBox "Insert code here for range A8:D10."
    Cancel = True
    Case Else
    Cancel = True
    End Select
    End Sub

    It won't unhide the fourth row for me. One addition to this I need though is I need it to hide rows 4,8,9 but I need to keep these rows hidden initially.

    Thanks again for your great support Tom,

    Bharat

  8. #8
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm still not sure of what your are trying to achieve when you double click B3, but try this modification of your code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Address
    Case "$B$3"
    If Target = "" Then
    Cancel = True
    Else
    Range("2:10").EntireRow.Hidden = True
    Range("4:4").EntireRow.Hidden = False
    Cancel = True
    End If
    Case "$B$4"
    MsgBox "Insert code here for range A8:D10."
    Cancel = True
    Case Else
    Exit Sub
    End Select
    End Sub

    You had a few unnecessary lines in yours. This will hide rows 2:10 and unhide (only show as visible) row 4.

    Does this get you closer to what you want?

  9. #9
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, you are so awesome this get me really close.

    Thanks so much.....

  10. #10
    Board Regular
    Join Date
    Jul 2002
    Posts
    591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Tom:

    What code would I use to unhide only a block of cells. So in the code you provided me you showed me how to unhide an entire row: so for example to hide row 4 you use the following code:
    Range("4:4").EntireRow.Hidden = False

    What if I wanted to unhide a block of cells only so for example A102:B105. How would I do that?

    Lets say I wanted to unhide only A102:A105 and C102:C105.

    Thanks again for all of your help Tom,

    Bharat

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com