A drawback to using project and task drop-downs in the calendar sheet is that maintaining valid input is tricky. For example, if you select a matching project/task pair and then change the project, the task is technically invalid. What should happen in that case? Note, this is not a problem specific to Google Sheets, but rather a general computer programming problem.
A simpler and error-free approach is to combine the two drop-downs into one so that only valid project/task pairs can be selected. And, it so happens to be easier to implement than synchronized drop-downs anyway!
To do this:
- Add a new column to the tasks sheet which concatenates the project and task columns. Here's an example formula:
=concat(A1, concat(": ", B1)). The output looks like this: Project A: Job 1 for project A.
- Use the new column in the tasks sheet as the data-validation criteria for you task column in the calendar sheet.
- Delete the project column from the calendar sheet.
So what you end up with in the calendar sheet is a task column with entries like this:
- Project A: Job 1 for project A
- Project A: Job 2 for project A
- Project B: Job 1 for project B
Hence, it's impossible to mismatch the project and task.