sumif indirect or vlookup?

[hdubz]

Hello,
I am new to this forum and am really hoping some wonderful excel expert can help me.
I would be unbelievably grateful!
I am trying to create a formula for a spreadsheet I use for calculating hours and wages in a shop. I have attached a smaller version of the one I use. The actual one I use has 52 tabs for each week whereas this one I have only included 3 to make it simpler and hopefully the formal can just be added in after a new week. I want to be able to sum in the FINAL SUMMARY tab the total hours for each week and then work out total pay etc. I have tried to use sumif indirect and I have tried a vlookup but I cannot seem to get it right so I was wondering if someone might be able to look at the spreadsheet and see if they can come up with the answer. I have puzzled for days now and I am sure it is really simple.
I want to be able to look in each tab and at the specific name of the employee as the name moves cells when they leave or when there are more people working but they are in the same column.
Any ideas would be great.
Thanks HDubz

[FDibbins]

Hi hdubz and welcome to the forum

In C2, copied down and across, use this...

=IFERROR(VLOOKUP($A2,INDIRECT("'"&VALUE(MID(C$1,SEARCH(" ",C$1,1)+1,2))&"'!$A$3:$J$17"),10,FALSE),0)

[hdubz]

Thank you so much thats brilliant! It works a charm. x

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)