Forum Discussion
Consolidating data from multiple worksheets
Hi,
I currently have a spreadsheet where each employee has a separate worksheet, I then have a master sheet showing the overall totals for each project. What I would like to know is if it's possible to look up the data from the employee tabs and enter it into the master as a total. i.e. to show the total number of hours for each project in one formula and the member of staff that is allocated with another formula.
thanks in advance
I think I can do it with a couple of 'helper' tables.
First table is a list of worksheet names to be used in the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&worksheets[Column1]&"'!"&"B3:B9"),Project!B2,INDIRECT("'"&worksheets[Column1]&"'!"&"C3:C9")))
and the second table builds a matrix of who's working on what, and then combines it into one string that your table can then reference. I've attached a copy, you can move the extra tables I've created to another worksheet to make it tidy if you need. Just put that worksheet at the very right (end) of your worksheets.
Hopefully that does what you need, or at least gets you closer..
I think I can do it with a couple of 'helper' tables.
First table is a list of worksheet names to be used in the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&worksheets[Column1]&"'!"&"B3:B9"),Project!B2,INDIRECT("'"&worksheets[Column1]&"'!"&"C3:C9")))
and the second table builds a matrix of who's working on what, and then combines it into one string that your table can then reference. I've attached a copy, you can move the extra tables I've created to another worksheet to make it tidy if you need. Just put that worksheet at the very right (end) of your worksheets.
Hopefully that does what you need, or at least gets you closer..
Hi Philip,
Thank you so much, this is exactly what I needed.
Machala
- Could you sum each employee's hours in each project they worked on, keeping those project totals in the employees' sheets keeping each project employee total cell consistent on all employee shees then just sum those totals vertically onto the master by electing all employee tabs and auto summing the employee totals onto the master?
