Horizontal and vertical lookup with sum across multiple worksheets

Hi,

I am totally out of my depth here! I have a workbook with multiple worksheets each showing a client and how many days each week each employee is assigned to it. Someone may be assigned to 2 days with one client, and 3 with another. Or more complicated.

My (very small) company have asked me to create a summary sheet on excel. I know I want to do a look up but it needs to be both horizontal for the name and vertical for the date. Then create a sum. But I don't know where to start.

The format of the worksheets can be changed to make it possible to do the calculations - we are not precious about that. We just need to keep in the info of how many days are assigned at each rate, and then who is assigned against each project.

If someone has any advice I would appreciate it.

Thanks!

Jane

OK, so only taking into account columns M to Q with names in, I need to see for example how many days Helen has assigned against her name for 15/9/14 across all clients. The problem is that Helen's name is not always in the same column, so I can't just put a filter on.

Now I'm thinking that a way round would be to pre input all the names onto the spreadsheet so that they are always in the same column, then I could filter. But my desired result is to have a number "magically" appear in the box against each name to say how many days' work they have been assigned that week.

Does that make sense?

Thank you so much. I am going to do a working test of it once I have updated each spreadsheet with the names and I will See how it goes. One question - will the Macro pick info from an infinite number of sheets?

Thanks!

Jane

I'm getting there! Just realised that I have to add in more columns with names to make it work (example on the Client 1 tab - this is the full number of members of staff)

But when I run the macro now there is no date data for the clients where I have added columns. I tried to understand the macro but have no idea what it all means to try to change it. I have included an example file with client 1 correct and the summary sheet with the Macro run.

The date is also defaulting to the 5 digit number which I can change manually but don't know how to manage in the Macro. Any ideas?

Again, thanks so much for your help!

Jane