Solved: Dependent drop-down lists with updating data

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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.
 

Attachments

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.
 
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.
 

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.
 

Attachments

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.
 

Attachments

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.
 
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.
 

Attachments

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!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top