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?
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?