Forum Discussion
Solved
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 poss...
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?