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:
Drop Downs
contains lists that are used to populate the dropdowns in the sheetPaul
Paul
tracks Paul's hours for the week.
1. Drop Downs
Drop Downs
has 3 columns containing the following:
Jobs
contains a list of about 30 job/site names.Work
is a list of about 70 work items/tasks.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:
Day
lists the weekday (eg. Thursday).Hrs Ttl
is the sum of theHrs
column for that day.Date
is the date "dd/mm/yy"Job
contains a dropdown from theJobs
range:'Drop Downs'!A2:A
Work
contains a dropdown from theWork
range:'Drop Downs'!C2:C
Hrs
contains a dropdown from theHrs
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.