How can I make a dependent dropdown?

Question

I work for a builder. I'm attempting to keep track of work completed by labourers at job sites. I'm trying to create dependent dropdowns.

I have two sheets:

1. Drop Downs contains lists that are used to populate the dropdowns in the sheet Paul
2. Paul tracks Paul's hours for the week.

1. Drop Downs

Drop Downs has 3 columns containing the following:

1. Jobs contains a list of about 30 job/site names.
2. Work is a list of about 70 work items/tasks.
3. Hrs. is a sequence of 50 times: 0.25 to 12.5 hrs in 0.25 hr increments.

Sample Drop Downs

	A	B	C	D	E
1	Jobs		Work		Hrs
2	-		ADMIN Meeting		0.25
3	#Admin		ADMIN Study		0.50
4	#Finance		CEILING Cornice		0.75
5	#Holiday		DECK Balustrade		1.00
6	#Meeting		DECK Handrail Post		1.25
7	1 Road City		FIXOUT Arcs		1.50
8	2 Road City		FRAME Bracing		1.75
9	3 Road City		PREP Scaffold		2.00
10	4 Road City		ROOF Beam		2.25
11	5 Road City		CLEAN Pack up		2.50

_{Drop Downs Sheet    (click image to rotate & enlarge)}
(Click image to Rotate and enlarge)**

2. Paul

Paul contains tables where Paul's hours are recorded. There is one table for each day and all the tables share the same headings:

1. Day lists the weekday (eg. Thursday).
2. Hrs Ttl is the sum of the Hrs column for that day.
3. Date is the date "dd/mm/yy"
4. Job contains a dropdown from the Jobs range: 'Drop Downs'!A2:A
5. Work contains a dropdown from the Work range: 'Drop Downs'!C2:C
6. Hrs contains a dropdown from the Hrs range: 'Drop Downs'!E2:E

Sample Paul

	A	B	C	D	E	F
1	Day	Hrs Ttl	Date	Job	Work	Hrs
2	Thursday	8	22/09/22	1 Road City	DECK Handrail Post	4
3				1 Road City	DECK Handrail Post	4
4
5	Friday	9	23/09/22	1 Road City	FIX OUT General	4
6				14 Road City	CLADDING	4
7				#Travel	ADMIN Meeting	1

_{Paul Sheet    (click image to enlarge)}

At the moment the Job dropdown is populated with a list of 30 job sites but Paul is only working at a few of them. Also the dropdown Work is populated with about 70 items but only a subset are performed per site and they vary from site to site.

What I want to do is limit the Job list to those that Paul works on and have the Work dropdown be dependent on the Job selected. That way Paul can choose the right Job and the Work options will be limited to a few options rather than scrolling through a long list each time.

Link to example sheet: Red tabs denote the original problem; Green tabs the current solution.

Answer 1

Setting aside the specifics of your use case, the way to create dependent dropdowns is approached the same way.

The first dropdown is used as a FILTER for the options for the second dropdown and so forth.

Assuming your dropdown Jobs is populated from a list in lists!A2:A:

	A	B	C	D
1	Jobs
2	Job 01
3	Job 02
4	Job 03

Your dependent dropdown tasks need to be filtered based on the Jobs category selected therefore the tasks must be associated/tagged with their appropriate category. You could approach creating the association in various ways.

All of the following achieve the same result:

1. Create a separate task list for each job category;
2. Use a common task list and a checkbox for each job category;
3. Create a two-column list with 'category:task' value pairs

Whichever approach you take, associated tasks with a Job category allows returning a list of tasks based on a selected Job category.

An array formula is placed in a helper column that checks what value is entered in each of your Jobs dropdowns and then returns an array of lists, one for each dropdown/row.

For example, assuming your Jobs dropdowns are in sheet data in the first column, data!A2:A, below are the example formulas that are used to populate your dependent dropdown.

The formulas are similar but adapted depending on the approach you take to managing the relationship between your tasks and their Job category.

1.   Separate list for each category

	A	B	C	D
1	Jobs	Job 01	Job 02	Job 03
2	Job 01	Task 01	Task 01	Task 02
3	Job 02	Task 02	Task 03	Task 03
4	Job 03	Task 04	Task 05	Task 04

    Separate list formula in lists!E2

=BYROW(data!A2:A, LAMBDA(r, TOROW(IFERROR(
   INDEX(B2:D,,MATCH(r,B1:1,0))),1)))

2.   Common task list and checkboxes

	A	B	C	D	E	F
1	Jobs	Tasks	Job 01	Job 02	Job 03
2	Job 01	Task 01	☑	☑	☐
3	Job 02	Task 02	☑	☑	☑
4	Job 03	Task 03	☑	☐	☑
5		Task 04	☐	☑	☑

    Common task list formula in lists!F2

=BYROW(data!A2:A, LAMBDA(r, TOROW(IFERROR(
  FILTER(B2:B,INDEX(C2:E,,MATCH(r,C1:1,0)))),1)))

3.   Two column 'category:task' value pairs

	A	B	C	D
1	Jobs	Category	Task
2	Job 01	Job 01	Task 01
3	Job 02	Job 01	Task 02
4	Job 03	Job 01	Task 04
5		Job 02	Task 01
6		Job 02	Task 03
7		Job 02	Task 05
8		Job 03	Task 02
9		Job 03	Task 03
10		Job 03	Task 04

    'category:task' formula in lists!E2

=BYROW(data!A2:A, LAMBDA(r, 
  TOROW(FILTER(C2:C,B2:B=r),1)))

Similarly if you wanted another dropdown dependent on the Task you would approach it the same way however now the Task would become the category for other lists of values.

Answer 2

This is relatively straightforward.

Assuming you are using data validation for your dropdown, the first dropdown work site is already working. What you need to do is to create an additional table that has all the workers that can be assigned to that work site.

You can then use the FILTER function for a site like:

=FILTER(B1:B7,A1:A7=$D$9)

where B1:B7 contains the names of the workers, A1:A7 contains the work sites that they can be assigned to, and D9 contains the work site selected in the dropdown.

I've created a simple example: Link to example spreadsheet