Aug 03 2018 05:04 AM
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
Aug 03 2018 06:14 AM - edited Aug 03 2018 06:15 AM
SolutionI 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..
Aug 03 2018 06:25 AM
Hi Philip,
Thank you so much, this is exactly what I needed.
Machala
Dec 19 2018 09:42 AM
Aug 03 2018 06:14 AM - edited Aug 03 2018 06:15 AM
SolutionI 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..