Using INDEX MATCH or similiar function to SUM over multiple worksheetes in a workbook.

[jonsj123]

Hello

I am running a time sheet and right now I am using INDEX MATCH in the individual sheets to get the information from our staffing program to this workbook.
=(IFERROR(INDEX($O$229:$O$441;MATCH(B11;$N$229:$N$441;0));""))

It is matching the name to the hours and displaying them in the sheets. It works great.

Then in a sheet called "Monthly Report" summing up the hours for overtime with a simpel 3D function:
=SUM('01.03.2018:03.03.2018'!F5)

The problem is that the people has to be on the same cell in every singel sheet for the calculations to be correct which even with my best efforts, require quite a bit of work at the end of the month.


Is it possible to a similiar function to INDEX MATCH but over a multiple sheets(the month)?

I tried it with vlookup but it requires me to make a vlookup function for everysheet and to rename it for each month. Which would be a massive amount of work.

I have attached a dummy sheet which I hope will be enough to show what I have now.

I been struggeling(googeling) this for a while and it would be great if anyone has a good solution

[Glenn Kennedy]

I rearranged the staff an changed the numbers, so that manual checking is possible (a bit difficult if everyone totals 24):

=SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!C5:C10"),A4,INDIRECT("'"&List&"'!F5:F100")))

where List is a named range (CTRL-F3 to edit/view) containing a list of all sheets to be summed.

[jonsj123]

It works! I did not even know the list function existed.

Thank you very much

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.