I am looking to automate our timesheet process. Each staff member has their own separate Google Sheet which has the dates down one column, and then a varying number of columns named after different projects they are working on.
I want to create an automatic summary of the total number of hours by project by staff member in another Google sheet. Like this:
I know I can use importRange to pull in numbers from specific cells across a number of different workbooks. But I am struggling to find a way to sum the hours by project without making every staff member have all 60+ projects on their timesheet and keep these in the same order so the cell references never change and break the formulas.
I want a formula which sums numbers in a column IF the header of that column is a specific name e.g. = Project 1.
So the range of the formula would cover a whole spreadsheet, but would only sum 1 column based on the header.