Forum Discussion
Lookup and consolidate data from multiple worksheets
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.
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