Sum data from multiple sheets using vlookup

[venomxx6]

I love Excel, always trying to learn more.

I have a excel doc we use at work to create a schedule for our technicians.
Each sheet is a week, so the sheet names are Week 2, Week 3, Week 4.
Each technician number is listed in column B (3,4,5,36,53,91, etc)
Row 5 has Mon-Sun
The techs work schedule is in the appropriate cell. (8 to 5, 10 to 7, Vacation, etc)

I want to count the number of shifts for each tech each week, with a running total for the year. It was easy to do it on each sheet with a simple countif formula. But The problem comes when I want to count them for the whole year.

I tried creating a "stats" sheet and make vlookup formulas to call the data from each weekly sheet. But with all the techs and shift types I want to count, it was like 40,000 cells. Excel wasn't able to calculate it, it had the "processing 0%" in the taskbar.

I thought I could use =sum(Week1:Week52!AZ6:BN50), which is where I have the counts from each weekly sheet. But my data is not always in the same spot on the sheet. Because of techs coming and going (new hires, people quit).

I have looked online for many hours for different solutions. The two methods above are the closest I have come to an answer. I would appreciate some help from the masters. This is my first post. I don't see a place to attach a copy of my document. I'm sure that would make it much easier for you to help me. How can I do that?

 

[bewsh1987]

little bit difficult without seeing what your workbook looks like, not sure how to add it to the forum or if you can

try working with the below

in A2 I have the 1st sheet name E.g. Week 1
in B2 I have the range E.g. A1:A3
=SUM(INDIRECT("'"&A2&"'"&"!"&B2,TRUE))

=INDIRECT("'"&A2&"'"&"!"&B2,TRUE) puts a range together Answer 'Week 1'!A1:A3

hope this helps

 

[venomxx6]

Week 1 sheet
Tech	Mon	Tue	Wed	Thu	Fri
3	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
6	8 to 5	8 to 5	8 o 5	8 to 5	8 to 5
36	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
53	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
Week 2 sheet
Tech	Mon	Tue	Wed	Thu	Fri
3	8 to 5	8 to 5	8 to 5	8 to 5	Vaca
6	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
36	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
53	10 to 7	10 to 7	10 to 7	10 to 7	10 to 7
Week 3 sheet
Tech	Mon	Tue	Wed	Thu	Fri
3	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
6	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
36	8 to 5	8 to 5	Vaca	Vaca	8 to 5
91	8 to 5	8 to 5	8 to 5	8 to 5	8 to 5
Stats Sheet
Tech	8 to 5	10 to 7	Vaca
3	14	0	1
6	15	0	0
36	13	0	2
53	5	5	0
91	5	0	0

<tbody>
</tbody>

From what I understand, in that formula, the data would need to be in the same place in each sheet.

Why is there no option to insert an Excel doc... on an Excel forum!?

I will try to use the insert table option to better express my data.

Note that the list of techs changes. So the data is not always in the same place form sheet to sheet.

This simplified version, I have 30 techs, and many different shift types.

The stats sheet is the one I want to automatically count the number of each shift type for all 52 weeks of the year.