Horizontal dependent drop down list with unequal amount of supervisors.

[Excelquestion35]

Hi all,

Trying to create a dependent drop down list, but ran into some issue. I want to create a drop down list that is dependent on a previous drop down list. The first drop down list includes the management unit, which is already working fine. For that I created a small macro that retrieves the unique values and paste there somewhere hidden and uses that list for the drop down.
Now I am dealing with the second drop down. This should be the list of supervisor that belongs to a certain management unit. Thus Supervisor 1-13 should be visible when Customer 1 was selected in the previous drop down etc.
What is a good way to deal with this situation?
Important to mention: The list of supervisors should be dynamic, in case a new supervisor will work for customer 1, this person will be added as a new row.

I know that you would normally could use OFFSET & MATCH for this, however I only used that with lists per column. I tried to use this same work around but I seem to be getting errors. The best result I can get is that I get all management units when Customer 1 was selected. Yet, I want the list of corresponding supervisors.

 

[Toadstool]

Hi Excelquestion35,

I'm not following why you can't use OFFSET and MATCH?

Excelquestion25.xlsx
	A	B	C	D	E	F	G	H
1							Customer	Supervisor
2							Customer 2	Supervisor 16
3		Management Unit	Supervisors				Customer 3	Supervisor 18
4		Customer 1	Supervisor 1				Customer 1	Supervisor 1
5		Customer 1	Supervisor 2
6		Customer 1	Supervisor 3
7		Customer 1	Supervisor 4
8		Customer 1	Supervisor 5
9		Customer 1	Supervisor 6
10		Customer 1	Supervisor 7
11		Customer 1	Supervisor 8
12		Customer 1	Supervisor 9
13		Customer 1	Supervisor 10
14		Customer 1	Supervisor 11
15		Customer 1	Supervisor 12
16		Customer 1	Supervisor 13
17		Customer 2	Supervisor 14
18		Customer 2	Supervisor 15
19		Customer 2	Supervisor 16
20		Customer 2	Supervisor 17
21		Customer 3	Supervisor 18
22		Customer 4	Supervisor 19
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G2:G5	List	Customer 1,Customer 2,Customer 3,Customer 4
H2:H10	List	=OFFSET($C$3,MATCH(G2,$B$4:$B$999,0),,COUNTIF($B$4:$B$999,G2))