How to merge multiple lines on a time sheet?!

[gloriousglenn]

Hi guys and gals,

I have an Excel book (simplified version attached) with an employee schedule that is tied in with the hours sheet, this is then consequently tied in with pay and billing.

Sheet one shows the 'Schedule' and if I change the hours within the schedule it is automatically updates the details on the 'Hours' sheet. However, if you look at the 'Hours' sheet, what I would like is that where an employee is shown on multiple lines, his hours are shown on just one line, with the other lines removed. e.g. Doe, John would be on one line showing his total of 43.5 hours.

I am assuming I need to use the =IF command linked to the Employee Number in column A, but I can't seem to figure it out!

Any help would be greatly appreciated.
Kind regards
Glenn

[JBeaucaire]

On the sheet Hours, do not use formulas for the names, enter the names/empnums one time each. Then you can use a simple SUMIF() to bring over the hour totals.

[gloriousglenn]

Thanks for that, it is probably 95% of what I am trying to do, and does help out tremendously (you can tell there is a 'BUT' coming!)...

...but, my problem then comes when there are changes. When there are changes to the schedule I want to be able to make the amendment once, and not on multiple sheets. e.g. If another employee (99999 - Richardson,Glenn) come is to take over Doe, John's shift on Sunday between 1900 and 2359, I would amend the 'Schedule' sheet (see attached), Doe, John's hours are updated, but it doesn't show Richardson, Glenn anywhere.

I realise that I could go in and add Richardson, Glenn manually into the 'Hours' sheet and all the hours are there, but I am trying to do it so that only one change needs to be made.

Any thoughts?

[JBeaucaire]

On the Schedule I've added a named range called EmpCodes (press Ctrl-F3 to see it defined). This is a dynamic range that will expand itself based on how many employee codes are in column A starting at A3.

Then on the HOURS sheet I put in an array formula in column A to collect the unique employee codes and display them. A VLOOKUP in column B brings over the name and the SUMIF formulas stay.

I also made some tweaks to your date setups at the top, change the date in C1 to any date and see.

[gloriousglenn]

That looks AMAZING! Wow, thank you so much. I am going to go away and try and implement that into my 'working document' and see how it goes (and then I'll come back and change to 'SOLVED'). I would never have figured that lot out in a million years. Thank you so much.

[JBeaucaire]

The two formulas found on the HOURS column A are array formulas. Any changes to the formulas must be confirmed with CTRL-SHIFT-ENTER, not just ENTER.
After changing the second formula (if needed), CTRL-SHIFT-ENTER to finalize the changes, then copy that cell downward.

Remember the first array formula is different from all the others.

[gloriousglenn]

Talk about a learning curve! JBeaucaire, thanks so much. Now...Can I ask if we can try and take this one stage further?

On the attached is everything you have done, but I have added a second work site to the 'Schedule'. How can we get it so that the Site 1 details goes into the 'Site 1' sheet and the Site 2 details go into the 'Site 2'? I think if this can be managed I will have all of my bases covered...I think!!

[JBeaucaire]

Yeah, I wouldn't do that. From a design perspective I wouldn't do any of this. Separate site schedules and hours report would all be on one sheet for me, then Site 2 schedule and hour would be the next sheet, wouldn't do this back forth stuff at all. More plumbing than truly necessary.

But I'm sure you like it, so best I would offer is to go side to side, not top to bottom.

[gloriousglenn]

Ok...So I had the weekend off from this and got a load of other stuff done yesterday....So lets see how you guys (Jerry!) can help me this week?!

The only reason reason why I don't have separate site schedules and hours report on one sheet is because I need to be able to view, and print, the schedule on one page so that I can have a complete overview. If there was a way of being able to do this, then that would be great! The way I would prefer (but not essential) to view the schedule would be like the attached. Week 1 of the pay period on the left and Week 2 of the pay period on the right. But this Schedule can be redesigned no problem.

Once I have finished it I will have approximately 20 sites/locations, so being able to add sites/locations to the Schedule tab would need to be quite simple. I would then also need to be able to add 'Site' tabs which would then link up to the "Schedule' tab like all of the others. This is because I very frequently get one off jobs that change regularly.