Help adding data from multiple sheets

[Raxstone]

Hello,

I have a workbook with multiple sheets. Each sheet is one 'job'. Each job can have several different employees in column A, and then the rest of the columns are for hours worked (example col B is Monday day shift, C is Monday afternoon, D is Tuesday day shift, etc).

I want to be able to make a new sheet, where I will list all employees in column A. Then column B will search for them in the other sheets (the Jobs) and total up their hours from each day.

ie. search for employee "A" in each job, as they are found accumulate the hours from column B (Monday day shift).

I am trying to use Index and Match with the sheets of jobs in a Named array ("Jobs"), but having no luck. I don't want to post every employee on every job to keep their row static, as there are over 100 employees but only 5 to 10 will work on any one job.

The below works if I specify a job sheet, but of course it doesn't allow me to go across all jobs and accumulate the hours:
=SUMPRODUCT(INDEX('19-SPEC190013'!$B$24:$B$100, MATCH(A13,'19-SPEC190013'!$A$24:$A$100,0)))

Tried below but no success:
=SUMPRODUCT(IFERROR(INDEX(INDIRECT("'"&Jobs&"'&!"&"$B$24:$B$100"),MATCH(A4,(INDIRECT("'"&Jobs&"'&!"&"$A$24:$A$100")),0)),0))

 

[Raxstone]

For a clearer understanding of what I am after:
Sheet 1 is an accumulator to track individual employees over each day

Name Monday Tuesday Wednesday ....
Bob
Vince
Janet

Then there are multiple job sheets (can be 20 to 200 jobs in any given week) which can vary in quantity (a blank sheet 'FirstJob' then each job sheet, followed by a blank 'LastJob').

Each job sheet can be:

Name Monday Tuesday Wednesday ...
Bob 2
William 3
etc

I need a simple formula on the accumulator sheet, for example on Monday, it searches each job sheet (Firstjob:lastjob) in column A (A24:A100) for Bob. If Bob is found, take the hours from column B for row where bob is found. Accumulate the hours from Monday from each sheet where Bob is found (Bob can be on any row on each sheet). There are over 100 employees, and their names are only entered on a job sheet if they did work during the week.

It shouldn't be difficult but I just cannot get anything to work.