Dropdown Selection based on Specific selection from database - dependent variable? INDEX

Hi There,

I am trying to have a dropdown list that will only display options that are specific to the value in the adjacent column. I want to be able to do this down the entire column - not just for one instance.

So an explanation of the objective! Well, we are trying to develop a tool for measuring worker efficiency at various tasks.

For each product we send out the door, there are a number of operations which must be completed e.g. trim part, polish axle, glue, assemble, place in bag/box.

The plan is to input what each person is doing daily. This will be in a list, row by row. They will enter their clocking number, date & start time for task and time that they finish. They will also enter the product name (from a dropdown list), and then in the column next to this, they will choose from another dropdown list the operation that they will be working on - which will be specific to the product they have selected. This information will be stored in another worksheet (Which I've called 'DATABASE').

There are 1,000+ variations in products and each one may have up to 15 operations. The operations will be specific to each product - e.g. "trim 42A-CB spindle body" or "polish outer body surface". Once these have been selected by the user, the average time for the operation will be retrieved & displayed in the next column. This can then be used to compare how well the operator is performing.

I am stuck as to how to have the 2nd dropdown list display only the Operations specifically for the product selected. Also, I then need to figure out how to select the average time based on this (although I think I can get this to work by combining the two columns e.g. =INDEX(Code, MATCH(A2 & B2, Products & Operations,0)).

Below I have tried to represent the relevant parts of how I think the database will/should be constructed. I've also included sample sheet. There are two tabs - the database where all info on operations & average times will be stored, and the input sheet which will effectively be a long list of all the tasks that operators have completed over the year (~30/day) which can be used to assess their performance.

So, in summary, please can someone give me advice as to how to set up a dropdown menu for column B that is specific to the selection of column A? I need to do this on each row, and am wondering if it can be done with formulae alone.

I've found something close to it (can I mention/link a different website?) using arrays, data validation & the INDEX match formulas, but this only appears to work for one value at a time - I am looking for something that will work for all the rows.


Example below...


So if we pick a particular product, say an A10V bottle trap, there are several assembly operations that are required before it can be shipped out the door. Let's say, three Operations must be performed, and we know the average time required to complete each operation...

A B C
1 Product......Operation............................................Avg Time (secs)
2 ABV10V.....VALVE AND CAP.........................................15
3 ABV10V.....TEST........................................................20
4 ABV10V.....ABV10V BAGGED.......................................12
5 CH120CL...APPLY RW-HN to Axle H1............................22
6 CH120CL...CLAMP CH2600CLBODY to Flange.................31
7 CH120CL...1/8 UNC INSERT ADDED TO TUBE................25
8 CH120CL...O-RINGS ADDED TO SLEEVE........................56
9 CH120CL...TUBE ASL WITH Micro SWITCH + Sealed.......80
10 CH120CL...CH2600CL ASSEMBLED...............................14
11 CH120CL...F10WRA-S-KIT.............................................5

Example!
So Employee #101 is going to be Bagging ABV10V (Row 4 on the database). The start time and end time will be filled in by their supervisor, allowing us to see how long it's taken them, We can then work out how many parts/ hour they've been able to make their way through or the average time per part bagged. We can then compare this to the average and see whether they are slow or quick.

I hope someone can help - it's been driving me slowly mad for days!!

Example.xlsx