Summarize sheets with a twist

Hello, I just registered to this board hoping to get some help on a specific problem. I have spent a few hours looking for some example that could help me but with no sucess. Anyway, if anyone smarter and more knowing than me has some time to kill, looking to improve his/her karma or whatever, here's the situation:

An organization is performing a number of activities. Each activity is represented by a sheet (this has to be). On each of the sheets representing activities, there is a range (a dynamic list of people that I have named) of personel and you're supposed to put the number of hours spent on this activity in the column to the right whereby the cost of this time is calculated by reference to a sheet with hourly wages.

The problem arises when I want to summarize the total time put in by each person. I could use a 3d-refereence like SUM(sheet1:sheet10!A1), but if the list of personel was changed, the cell references would be incorrect (adam is on A30 instead of A31 since eve was added to the list of employed).

I realise that this is a very porly designed workbook, but it has to look this way because of reasons having to do with the incompetence and inability to use computers of the ones supposed to use it.

I anyone feels like helping it would be great!

I dont know how to post a better exemple than this in case anyone has problems understanding what I'm trying to say here:

Employee Hours Cost

Adam 5 50
Eve 7 45
George 8 60

The hours for each employee has to be summarized from the different sheets. And the twist is that the positions in the list might shift.

/ Bjorn

Bjorn

See if the attached file will get you started. I've build in a User Defined Function (UDF) to do the totalling. You will always need to have the summary sheet as the left most sheet, and it will action all the sheets to the right.

rylo