Forum Discussion
Consolidating data from multiple worksheets
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?
