Lookup and consolidate data from multiple worksheets

[djdeuph]

I am creating a resource scheduling workbook where each project has a tab and includes some project related data, the staff, and the hours each week assigned to them.  I have figured out to bring all of the data to a master resource view that then shows who is overscheduled.  Now I need to create a different view that can go through my project tabs and list the projects each resource is assigned to along with other project data. Any help would be most appreciated. 

[#2]

Hi @djdeuph 

My preferred approach for this is to use Power Query to consolidate the data and then present it in a format that can be sliced and diced with Pivot Tables

I've attached a suggested solution.

This requires you to Name the ranges on the various Project sheets and add that name into the small table provided.

Have you named ranges before or used Power Query?

Wyn

MVP

UTC+8

[#3]

Hi @Wyn Hopkins

This seems to be heading me in the right direction.  I'm not familiar with Power Query but am familiar with naming ranges.  In fact, already have a named range for the list of projects on the Resources tab (ShtLst).  In each project tab, the top part is the scheduling section.  Below this, I have placed some overall project data in a separate table.  Is it possible to pull this into the Consolidation?  For example, I want to Consolidation table to list each resource, the projects they are assigned to, the period end (row 21), and the budget (row 25).  I think I am missing a step.

[#4]

Hi  @djdeuph 

By dragging Resource into the Pivot Table you could see which Projects have been worked on by each resource

The other tables could be summarized with Power Query, but for those it's probably simpler just to set up a little summary table with formulas that point to the relevant cells.

I've been meaning to do a video on this for a long time and your question finally prompted me to do it... so if you'd like to see how I built your solution check it out here   https://youtu.be/cPN24NK3_68