how to show what projects each person is working on

[itsemeliii]

Hi we have a project spreadsheet, which all the projects we have going on in our department on it. We also want to add a workload tab which shows how many hours people are working on each specific project each week. We want this to show on a dashboard but to get the data from each individual tab for each project. 

Each project has its own tab where it shows all the detail going on within that work. Down the bottom to the right there is a project org list where they have a drop down for the project leaders. If one is selected for example ' Emily' and a value is put in to the next cell. How do we get that to show on the dashboard next to the name Emily for week 26? 

[#2]

@itsemeliii 

There is a variety of ways to do this (always true with Excel).

I have a workbook consisting of multiple sheets used to track various investment positions I have. And there's a single sheet that summarizes key data from each of the individual sheets. To a certain extent this parallels what you're describing.

One crucial aspect of mine--and I can't tell if this is true for yours--is that I've made an effort to be consistent with the placement of those "key data" elements on each individual sheet.  I then make use of the INDIRECT function to point to those key data elements on each individual sheet, constructing an address that consists of the sheet name and the cell reference (row and column). 

INDIRECT is a tricky function to master (not sure I have, even though I use it), but is powerful. Here's a webpage that explains it well. https://exceljet.net/excel-functions/excel-indirect-function

If you need more help, would it be possible for you to post an example of one or two of your individual project sheets (after rendering anonymous any names or other confidential info) as well as a simplified version of what you want for the dashboard? I ask because it might be easier for me or somebody else here to demonstrate the method with your actual layout, rather than to construct our own example...

[#3]

@itsemeliii 

Another method that occurs to me is to use the named range capability. A cell could be named "Emily_whatever" and then you can make a reference to it on your dashboard by just entering =Emily_whatever as the formula.

To name a range, go to Insert.....Name....Define

So much depends on the layouts of your sheets, the flexibility you expect, and so on. As I asked earlier, if you can upload a sample, devoid of confidential and personal info, that would be a great help in helping you.

[#4]

@itsemeliii  hard to tell without seeing some example, but you could use sumif formulas to sum a group of worksheets directly:

https://exceloffthegrid.com/how-to-sum-the-same-cell-across-multiple-worksheets/

I used this extensively to consolidate budget figures, each worksheet would be from a different division.