1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Dependent drop-down lists with updating data

Discussion in 'Business Applications' started by SpiffyMasta, Nov 15, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    Ok, so hopefully I will explain myself clearly enough that someone can help me out.

    I have an excel workbook which is essentially a timesheet and has two sheets, one is "time sheet" and the other is "jobs".

    The jobs sheet has information which is pulled from another excel file. This information includes 4 columns I use called company, department, friendly name, and quickbooks job. I've set up a dynamic name range which automatically includes new data in some lists I've created. One includes all the table's data, one list includes all the friendly name column data.

    Currently, the time sheet is all setup and has a drop down list which pulls from my list named friendlyname. This works great at the moment, because there aren't that many different jobs.

    I would like to make it easier in the long run for the user to find a specific job by choosing from two drop down list two items: the company and the department. Here the departments available would depend on what company is picked. From there the drop down list currently used to pick a job would only have the friendly name items which belong to that company and department.

    I've done tons of research online, and I am completely stumped as to what I need to do.

    Every job entry gets all the information needed on one row, including the company and department.

    Hopefully someone can help!

    Thanks
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Formulas in the attached rely on Jobs!A:D being sorted by "company".

    Note that the Jobs!F2 formula is "plain" whereas the F3:F7 formulas have a +ROW()-2 "add-on".

    Note that the Jobs!H1 formula is part of the dynamic named range "depts" [=OFFSET(Jobs!$F$1,1,0,Jobs!$H$1,1)]. It could be in the named range formula directly, I've just "split it out" for clarity.

    When you select from Time_sheet!B2 the B5 options update -- but it's not perfect because the first option for the previous selection remains visible.
     

    Attached Files:

  3. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    Thank you for this, I will test it out with my sheet and let you know if it works.
     
  4. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    Ok so this solves the issue of having only departments that fit the company category in the drop down menu. But what I also need is once the department is selected, the next drop down menu will pull the friendly names associated with those departments and company.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If there are potentially many "friendly name" options per department, we'd really need to see a layout sample to proceed.
     
  6. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    Here is a sample of my timesheet. I had to eliminate some of the bulk to make it small enough, but this should be all the data you need to help me out.

    Hopefully this helps you understand better what I am talking about.
     

    Attached Files:

  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, I tinkered with this some.

    Jobs!J1 is plain data validation, STC/STI.

    The dynamic named range Company is dependent on J1 selection.

    J2:J27 is an array formula for Company offset, so dependent on J1 selection.

    K2:K27 formulas pull the unique values from J2:J27.

    The dynamic named range Types is based on the K2:K27 formulas and drives the L1 data validation.

    Pulling "Product/Dept." according to "Company" and "Type" will need further thinking. It might be doable.
     

    Attached Files:

  8. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    Now I think you're understanding why I'm having such a hard time with this.

    Two things to note, I don't need the type to be used, the pattern will go like this

    Company gets selected -> Prod/Department gets selected according to prior choice -> Friendly Name is final selection according to previous two choices.

    Now another thing to remember is the jobs sheet gets updated automatically from another register which contains the data. There is a much higher number of jobs, but each has the same information when I add them in.

    If you think there's another way altogether I could do this let me know. I designed the system I can alter it.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "another way", code would be a cinch but I don't know if it's allowed.

    Try out the updated attachment. It's been driving me in circles so there's probably a fair amount of overkill. Any DV cells are highlighted yellow.
     

    Attached Files:

  10. SpiffyMasta

    SpiffyMasta Thread Starter

    Joined:
    Aug 4, 2004
    Messages:
    117
    This is perfect. I adapted it to my specific needs, but those formulas were exactly what I needed. Thanks a ton!
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Congrats on figuring something out from my ramblings. :D (y)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027038

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice