Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Pull (v2000)

    I have a drop down that is tied to a list of main office names. Each main office has varying numbers of satellite offices and within each satellite is a different number of employees. I want the user to select the main office from the drop-down and then underneath it, have each satellite associated with this main office appear. I would also like each employee to appear under the satellite office name. Lookups won't work so is there another way?
    Prost!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Pull (v2000)

    It would help if you could attach a sample workbook showing the layout of the data you have. The names can be dummies.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: List Pull (v2000)

    Does the thread starting at <post#=463,944>post 463,944</post#> help answer your question?

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Pull (v2000)

    Hi Hans,
    The attached is a sample. Cell A3 is a simple drop-down. When selected, the main office info. populates using a lookup. Underneath it, I would like the satellite offices to appear and the associated employees under each office reporting to the main office. I set-up a simple layout illustrating what I want to show. (Column H contains sample data to populate the left side of the sheet).
    Thanks!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Pull (v2000)

    I'd say that Access is much more suitable for this than Excel. I may have a look at it later.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Pull (v2000)

    Andrew

    I was looking at this in between calls at work and I think I have come to the same conclusions as Hans. It appears that Access is possibly the best tool for this type of thing as it is relational and can pick up the one to many relationships better and show your excel facsimile as a form. my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: List Pull (v2000)

    I had a chance to play with this.

    In Cell A3 instead of using Data validation (which does not always trigger events), I added a ComboBox from the control toolbox (not from Forms) to cover the cell A3.
    I also changed the formula in B3:F3 to adapt for this modification
    In B3 enter the formula:
    <pre>=INDEX($H$14:$M$15,$A$3+1,COLUMN())</pre>

    And copy B3 to C3:F3.

    After adding the combobox, select properties and change:
    LinkedCell:A3
    ListFillRange:$H$14:$H$15

    From the "ViewCode" you can see my routine (it is the sheet object in VB). The code will run when the combobox is changed. It will get the Office Code, clear the cells, then extract the satellite and employee info.

    I have attached an example. Adapt as desired...
    Steve

Posting Permissions

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