Pivot Table using multiple ranges

[GB]

Hi,
attached is a file that contains 2 worksheets (Qualification, Tasks) along with a 1 pivot table.

The "Tasks" worksheet contains daily activities of many staff in Nov2016. Each staff member is associated to a workgroup.

The "Qualification" worksheet contains the historial and future movement of each staff member. For every staff member a DateFrom & DateTo column may change if any of the 4 items change (Qual,Cat,Location,WorkType).

I have created a PivotTask worksheet.

What I am trying to do is integrate 2 worksheets into one pivot table using workgroup as the key:

• I want to create a daily view into the future e.g for the next 12 months that shows count of staff by workgroup. If I have activities known (like Nov2016) from the "Tasks" worksheet then show them by activity as per the attachment. If I don't have activities like Dec2016 onwards then at least I want to see the count of staff by date per workgroup integrated into this pivot somehow.
• How can I get the count by workgroup from the Qualifications worksheet integrated into same daily view pivot table view when this is currently organised by DateFrom, DateTo date ranges for each staff?

Your help would be most appreciated.
kind regards
GB

 

[Chihiro]

You can't use WorkGroup as key.

Key field for at least one of the table must be Unique list. Both your tables contain multiple duplicates for WorkGroup field.

If you have access to PowerQuery, you can merge two tables using multi column condition.

At any rate, what you are looking to do will be difficult without having calendar table (in data model) for PivotTable.

Alternate method is to use custom calendar sheet set up with dropdown to control workgroup summary being displayed.

 

[GB]

Thanks for your input. I thought as much. I will try PowerQuery or similar.
regards
GB