Collating/grouping data from different sheets

I think this may be something that's solved via a pivot table, but I'm not quite sure. It's something I could do instantly in a database, but I'm forced to use Excel at work, so I need to figure out a way of doing it in Excel!

I have a spreadsheet for the weekly rotas at work - one day to a sheet with a summary sheet at the end. On each sheet is a list of employees working that day and what hours they are working (e.g. "Dave | 12:00 | 20:00" (in A3-C3), "Fred | 10:00 | 14:00" (in A4-C4) and so on). From that, I have a formula that works out how many hours it is they are working, and from that I can work out the total number of hours worked by everyone that day (i.e. a sum of the hours each person works) and then the total number of hours worked (i.e. a sum of the total from each day) - which is used to see how many hours of staffing has been spent each week.

However, what I would like to do is to be able to have a 2nd summary sheet that lists all the staff that are n work that week and how many hours they are working in total. e.g. if Dave works Monday, Tuesday, Wednesday, and Friday from 12:00-20:00 on each of those days, & Fred works 10:00-14:00 on Monday and Thursday, it will say "Dave | 32" / "Fred 12", etc.

I'm sure this is remarkably easy for a semi-experienced Excel user, but I've always worked with databases rather than spreadsheets and so have no idea where to start, so any help would be appreciated!

Originally Posted by martindwilson

it would be a lot easier if you attached a workbook with examples!

The actual workbook is on the computers at work which have no Internet access, but I'll try to create something similar later & post it.

Okay, I've simplified my problem a little - rather than having to collate and filter data from different sheets, I've managed to collate everyone's hours onto one sheet - I've created a simplified version of this and uploaded it. There are two sheets in the upload - what I have at the moment (which is basically a list of how many hours each person works each day) and what I would like to have - which also sums up how many hours each person works that week.

I put all the info into one big column today and played around with filters and pivot tables, but the filter will only filter the list to show the rows that match the filer - there seems to be no way to sum the hours shown. As for the pivot table, All I could get it to do was count how many times the person appeared in the list - e.g. it would show "Dave 5", "Emma 2", etc, which is the number of days they've worked that week.

Originally Posted by martindwilson

if you put them in one column you can use a pivot and simply refresh data as new get added or just delete them all and put new week in and refresh see sheet 1

That's great - could you tell me how you did it? I tried exactly that at work today (putting everything in one column and using a pivot table), but whilst the first column was correct, in the second it only showed the occurances of each person's name in the list. Admittedly, I've never used Pivot Tables before, so I wasn't entirely sure what I was doing - I was looking at the "sumif" function as well, as it sounded like it did the kind of thing I wanted, but I wasn't quite sure how to combine the two, and having no internet access from work makes it rather tricky to search for things (and the excel helpfiles are not always the easiest way to find out how to do specific things)!

Cheers!

Ahhh - I spent ages messing about with it and was only a double-click away from it working! Thanks so much