Summarising data from numerous sheets

[ChrisMattock]

Good morning all!

I have 10 sheets for each member of staff. (Personnel Sheets)
I have 1 sheet with a list of projects. (Approved Projects)
There will be 10 sheets for each project. (Project Sheets)

Each of the personnel will then select a number of projects from a drop down list and assign hours for each day of the month to those projects.

I need the project sheet to count all the hours assigned to each project and enter them in the relevant Project Sheet.

To make it a little trickier the number of projects and staff will vary each month and therefore I'd really like the process to be as automated as possible.

See attached.

If anyone could give me a rough outline of the logical/best way to do this I am sure I could work the rest out on my own.

[XOR LX]

Hi.

Can you give a few examples of your expected results? And where are these results to go?

Regards

[ChrisMattock]

Thanks for the response!

Hi there - if you look at the worksheet labelled "3" in the file uploaded with this comment, that is how I would like each of the worksheets with a numerical label to populate.

[XOR LX]

So the list of names need to be generated from the actual worksheet names? This can only really be done using VBA.

Regards

[ChrisMattock]

I am happy to use VBA. I assumed I would have to.

Alternatively I could have a name within each of the Personnel Sheets...

[XOR LX]

Forgive me, but one of the biggest mistakes I see from people using Excel is the desire to use multiple worksheets to present data.

Can I ask why you want a separate tab for each individual PRN? It would seem to be far better, and especially since you seem to be reporting on just three different fields for each - Hours, Rate and Total - to have all this data in a single sheet. This would make consolidated reporting far easier.

Also, I assume that you have to manually type the name of any new worksheets you add? If that is the case, can't you add these names to some list in the workbook at the same time, thus removing the need for VBA to read the actual sheet names?

Apologies, but you seem to have set yourself up with quite a poor layout here, which, in my experience, generally leads to poor workbook performance and difficulty when it comes to reporting.

Regards

[ChrisMattock]

No need to apologise! I am fully aware none of this is ideal...

In this example I am only actually reporting on one field - hours. The rate will simply be a VLOOKUP from another sheet tied to the staff name and the Total will be a calculation of hours multiplied by rate.

However, I have simplified the task just to get the functionality that I need... each project page will be much more complicated adding in material, plant, subcontractors, markup etc.

So I think that each timesheet page for the members of staff is essential as this is how they will enter the data. And each project page is essential as this is where all of the information is pulled together, and indeed how it is billed.

As for manually typing the worksheets then yes, you are correct, and yes, this could be added to a list - but I don't understand how that helps me, I wish I could do a sumif accross all sheets with a persons name as the sheet title, but pretty sure that's not possible.

[XOR LX]

Still not entirely sure what you want, but perhaps this in B5 of tab 3:

=SUMIF(INDIRECT("'"&A5&"'!B:B"),'3'!$B$1,INDIRECT("'"&A5&"'!AI:AI"))

and copied down.

This is also copiable to the other tabs, simply changing '3'!$B$1 to '4'!$B$1, '5'!$B$1, etc.

I have no idea about the other two columns (Rate and Total) since you gave no expected results for these.

Regards

[ChrisMattock]

That's great! Is there a way of using INDIRECT for the middle value as well? So that the worksheet could be copied?

I tried =SUMIF(INDIRECT("'"&A5&"'!B:B"),INDIRECT("'"&B1&"'!"&$B$1),INDIRECT("'"&A5&"'!AI:AI")) but it doesn't work as I guess I don't completely understand the function.

[XOR LX]

Good point. Should've thought of that.

=SUMIF(INDIRECT("'"&A5&"'!B:B"),INDIRECT("'"&$B$1&"'!B1"),INDIRECT("'"&A5&"'!AI:AI"))

Regards

[ChrisMattock]

AWESOME! Thanks!

[XOR LX]

You're welcome!