Solved: Dependent drop-down lists with updating data

[SpiffyMasta]

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

 

[bomb #21]

Formulas in the attached rely on Jobs!A 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.

 

[SpiffyMasta]

Thank you for this, I will test it out with my sheet and let you know if it works.

 

[SpiffyMasta]

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.

 

[bomb #21]

If there are potentially many "friendly name" options per department, we'd really need to see a layout sample to proceed.

 

[SpiffyMasta]

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.

 

[bomb #21]

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.

 

[SpiffyMasta]

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.

 

[bomb #21]

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

 

[SpiffyMasta]

This is perfect. I adapted it to my specific needs, but those formulas were exactly what I needed. Thanks a ton!

 

[bomb #21]

Congrats on figuring something out from my ramblings.