Dependant drop downs filtering in three stages, department, employment type and finally em

[Hirad001]

Dear all,

I am trying to input data from a manual job card for a range of employee’s that will allow me to pivot the data and put out meaning full analysis. I attach a sample file to show what I'm trying to do.

I have three departments GI, RP and FLT
I have four employment types F/T, P/T, S-P/T and agency
All employee’s sit within one department but there employment type varies.

I was able to do a drop down for employment type and a dependant drop down for employees when the department wasn’t a factor; however I have tried to follow examples but to no avail when trying to filter the data in the sequence:

By department, then by employment type and to select from only those employee’s dependant on the choices made previously.

I thought using a table would make the solution dynamic when employees are either added or deleted?

Can anyone help?
Many thanks
Mark

[FDibbins]

Where are you using these DD's and can you give a few sample answers?

[Hirad001]

Hi FDibbins

I hope this is what you were looking for in your question

A4 is where I want to filter the department, which I haven’t setup as all the playing around I have done has achieve zero

Cell A9 is a dropdown for Employment type and has formula =HoursList
The dependant drop down is in A13 = Indirect($A9$) which bring up the list of empoyees that have work the selected employment type e.g. Full Time

On the worksheet Lists in N1:P45 I have list of department code, they the Hours and then the employee.

An example of what I am trying to achieve as an output is

Dropdown in A4 selects GI
Drop down in A9 select S-PT as the employment type
Which then shows in A13 Karen Hall

Or A4 selects RP
A9 selects S-PT
A13 drop down shows either Lindsey Fitzpatrick or KirstyWales as options



Mark

[FlameRetired]

An example of what I am trying to achieve as an output is

Dropdown in A4 selects GI
Drop down in A9 select S-PT as the employment type
Which then shows in A13 Karen Hall

Or A4 selects RP
A9 selects S-PT
A13 drop down shows either Lindsey Fitzpatrick or KirstyWales as options

I took a "stab" at this one. All I have are some observations I'll pass along.

I thought I had this part solved (in the quote). I got the drop-down in A13 to behave as you described but when I selected

Agency in A9 and GI in A4 the list populated with both Emmanuel Arseno and Tom Stone. It did the same thing

when I selected RP in A4. They don't both work GI and RP. The part I thought I had solved stopped working, too.

I investigated and observed that in Table1 Emmanuel Arseno is listed as both Agency and PartTime. However in

Table4 Emmanuel Arseno is only listed as AGENCY and not PartTime. (Table4 looked like a good reference for building

the selective list you described.) I observed some other anomalies as well but I didn't recognize their significance if any.

I don't know exactly what to suggest. I am reluctant to make any "adjustments" in Name Manager or your Data Validation defs.

There is too much in your Tables and definitions and how they interact that I don't understand.

Sorry I couldn't help but perhaps this will give you a place to start.

[stunn]

Hi Mark,

I've created a new table (calculated), and changed a couple of your named formula. Results here:

[Hirad001]

Hi FlameRetired

Apologises for leaving the defined names and Tables created in a mess, you are right I had moved to Table as the way to solve problem. Many thanks for trying to look at my issue and points raised are valuable in ensuring I prepare future requests and workbooks to make it clearer

Many Thanks
Mark

[Hirad001]

Hi Mark,

I've created a new table (calculated), and changed a couple of your named formula. Results here:

Steve,

Many thanks for wading through my clutter and understanding perfectly what I was trying to do. The formula for the new table (calculated) is brilliant and what I wanted but didn't know how to go about it, had spent hours trying to find a worked example that I could adapt in vain. I have cleared up the mess I made in the Name Manager. Just a cheeky ask; on the InputForm cell A13 is it possible show only the names available based on the choices in A4 & 9 and supress the "-" that appear from the calculated table?

Once again many thanks for great solution.

Regards
Mark:)

[stunn]

Hi Mark,

You're welcome, thanks for the feedback.
To supress the '-' change the 'Operative' named formula to:

=index(Table5[Operative],1):index(Table5[Operative],countif(Table5[Operative],"<>-"))

[Hirad001]

Hi Mark,

You're welcome, thanks for the feedback.
To supress the '-' change the 'Operative' named formula to:

=index(Table5[Operative],1):index(Table5[Operative],countif(Table5[Operative],"<>-"))

Hi Steve,

I couldn't get it to work, when replaced formula, is there something I am missing? I take it it worked for you?

Cheers
Mark

[FlameRetired]

Hi Steve,

I couldn't get it to work, when replaced formula, is there something I am missing? I take it it worked for you?

Cheers
Mark

Mark,

I took a different approach. My drop-downs now work. I made a separate non-table list (columns R:T in 'Lists') to work out the drop down. The dynamic named range in Name Manager "PersonnelAvailable" will take care of the "-"s and it eliminates the large number of blank spaces in the drop downs when the lists are short. Like Steve I changed the P/T F/T etc to PartTime FullTime etc as they appear in A9 drop-down.

The formula that generates the list is an array formula that does not have to be array entered. Just commit with Enter and fill down. In X1 of 'Lists'
this:

Please Login or Register  to view this content.

I filled down to row 52 to accommodate additions.

The formula for the DNR "PersonnelAvailable" is:

Please Login or Register  to view this content.

The file is attached. Let us know how it does.

[Hirad001]

Hi FlameRetired,

Sorry for the slow reply, just life getting in the way. What a fantastic solution and totally solves "-" from Steve's solution and elimination of blanks to keep drop downs clean. I did play around as I was hoping to keep using tables as additional personnel would be included automatically therefore modified your formula to this:

Please Login or Register  to view this content.

Once again thank you for your assistance and coming back to my thread especially after the clutter in my uploaded file.

Cheers Mark

[FlameRetired]

You're welcome....glad it works and thanks for the rep.