Get task list from other sheets based on specific criteria

[puneet829]

Hello experts,

I have 4 different tabs in sheets for 4 different departments, which lists different tasks. The format is same for all 4 sheets, having headers as Project Name, Owner, Area, IssueDate, Task Type, Status, Comments.

I need to list all the tasks in a new tab (Task List) which should list only the tasks from first 4 sheets based on the status and owner.
For example,
- from 1st sheet I want to see the task owned by John and whose status is either "On Track" or "Delayed"
- from 2nd sheet I want to see the task owned by Jacob and whose status is either "On Track" or "Delayed"
- from 1st sheet I want to see the task owned by Oliver and whose status is either "On Track" or "Delayed"
- from 1st sheet I want to see the task owned by Samantha and whose status is either "On Track" or "Delayed"

So basically all the running tasks owned by these 4 people should come in "Task List" tab.
Output table should be in same format as other sheets. See attached samplefile.

Can someone please help me in achieving this?

Thanks a lot.

Cheers
Puneet

[FDibbins]

This would be far simpler if all data was on the same sheet - is that an option you would consider?

[puneet829]

Hello FDibbins,

Thanks for replying to thread. I know putting on same sheet would help but different department has to use different sheets otherwise my many other objectives will not be achieved.
Would creating a dummy sheet in the end with all the entries form first 4 sheets help somehow?

[AliGW]

Why just the four people mentioned? What's the rationale for choosing them?

[puneet829]

Its because i need to see only the tasks which my team has. So i should be able to chose the task owner.

[AliGW]

How do we know which team each person is in? Where is this listed?

[AliGW]

PowerQuery Solution

I created a team list on the validation source tab and merged it with the merged data tables (this does not account for the fact that there appear to be two different Johns based on your sample results, so they would need identifying properly).

Main Query M Code:

Please Login or Register  to view this content.

Team Query M Code:

Please Login or Register  to view this content.

[puneet829]

For now I am assuming there will be no more than 1 name in different teams, so team is not important. But how you got the output in task list tab, that I did not understand.

[AliGW]

I just created that list myself based on the names you gave us. I needed this to filter to your team. Without it, I'd have had to filter the query to the four names, which would work fine, but isn't so reliable going forward if the team changes - better to have a lookup list somewhere for the query to reference.

[puneet829]

Yes, you are right. for future, it would be a good idea.
Can you tell me how to implement this?

[AliGW]

All you need is a two-column list - names in one column, team name in the other. That's it. Are you actually asking for the PowerQuery steps?

[puneet829]

No, I dont know about Power Query.
I am referring back to my original question. I am still standing where I was

I just need the output as i mentioned.

Thanks

[AliGW]

OK - so are you saying that you don't want to use the PowerQuery solution, even though it gives the output you mentioned?

[puneet829]

Ok, I will use it if you can tell me how to

[puneet829]

Can I ask you to make the changes as you said in the sheet and send to me? I would really appreciate it.

[AliGW]

There are no changes to make. Everything I know about your teams is already set up in the sample file. Or are you asking me to filter it on names instead of using the team list? I'm really not clear what you want and what you don't want.

[AliGW]

Here is a version that is filtered purely on the four names with no lookup list.

There are links in my signature line for you to follow to learn more about PowerQuery and how it works.

[puneet829]

What I want is....that in samplefile you sent me, if i change the task status to delivered, lets say in Dept1 tab, I change the status for task ABC1 which John has to "delivered", the output should not show me that task in list.
But when i change it, nothing happens to output list.

[AliGW]

You have to refresh the query (Data ribbon - Refresh All). This can be automated if you need it to be.

[puneet829]

It works. Thanks a lot.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.