Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupimportrange

How to compilate data from multiple sheets using query conditions

I'm trying to make my own Task Manager for my team. The idea is simple. I have one sheet (Sheet A) in that I write tasks for my team.

Here's a screen

So, I fill just columns B, C, D. Column A is fill up automatically. In B I choose from the list one responsible for the task. Column D I set a deadline for the task.

Now, I need to import these tasks automatically in Sheets of my co-workers. I have 8 members. So, every one of them has separated Sheet with his tasks (Sheet B, Sheet C, etc).

Here is the screen of *Sheet B* for example

How you can see, I import data from Sheet A with query function. Now my co-worker needs to fill Columns E, F & G. And I need to update his responses in my Sheet A.

The problem is that the tasks in Sheet A are mixed and if I'll use the same query function, the responses will now correspond to tasks.

And another problem is, how to export my tasks from Sheet A to other sheets dependently of the name of Responsible co-worker.

So.. I understand that my questions are not simple, but I hope I'll get help here.

Solution

  • you need this:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {
 IMPORTRANGE("ID 1"; "sheet name!A1:G");
 IMPORTRANGE("ID 2"; "sheet name!A1:G")}; 
 {5\6\7}; 0)))

    0

    spreadsheet demo