3

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.

0

2 Answers 2

2

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.

1

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

1
  • Sorry, I should have shared an example of my sheet. It has an example of what we're currently using but in the "drop downs" tab we'd like the "work" dropdown to be specific to the job site. docs.google.com/spreadsheets/d/…
    – Jonno
    Sep 26, 2022 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.