I have spreadsheet that contains three pages:


enter image description here


enter image description here

Tasks are related to Projects


enter image description here

Related to Projects and Tasks.

When I select Project in Calendar and go to Task drop down I have all tasks there. How to make dropdown that contains only Tasks related to selected in column B Project?


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:

  1. 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.
  2. Use the new column in the tasks sheet as the data-validation criteria for you task column in the calendar sheet.
  3. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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