Look for unique info in one column, show sum from multiple tabs / columns

[galvinpaddy]

Ok, so the title is a tad cyrptic!!

Workbook is a time sheet for 14 departments(i created it and all aspects of it in my own time at home so the sheet is mine)
Layout is identical on each tab.
)olumn B in each sheet contains employee name for that area (ranging from B9:B70)
Column Y in each sheet displays total hours worked for that area (ranging from Y9:Y70)

I would like another 'Summary' sheet that would look at column B (range B9:B70) on every sheet, and then show me all unique names (so no duplicate names) in one column, in another column i would like the same thing, but this time a sum of all hours worked for that name, taken as a sum from all the sheets where the name appears.
I also do not know if this would be better being done via macro??

Sample attached, notes are made on 'Summary Tab'
Kind Regards.
galvinpaddy

[nathansav]

Can you use a pivot table with multiple input ranges?

[galvinpaddy]

i am afraid i have no idea, i have almost no experience with Pivot tables

[galvinpaddy]

If someone wouldnt midn giving me an example on the pivot tables that would be appreciated

[nilem]

Hi, galvinpaddy
try it

Please Login or Register  to view this content.

[galvinpaddy]

That, is, brilliant.

Spot on with what i was after, very much appreciated fella!! thank-you very much indeed!

Some well earned rep heading your way!

[galvinpaddy]

Apologies Nilem,

With your code, is it possible to limit the amount of sheets it takes data from? for example if the workbook contains 15 but i only want to take data from 12.

Kindest Regards

[nilem]

then try so

Please Login or Register  to view this content.

[galvinpaddy]

Good morning Nilem,

In the attached (taken from post 5) I have added 2 more columns into the Summary tab (G = Sat & H = Sun)
Is it possible for you to modify your code so that the summary sheet shows:

Column B shows full total of hours (Mon-Sun)
Columns C:F doing what the already do, showing hours worked in an area mon-fri
Columns G:H showing me hours worked over weekends (a total of both days, doesn’t need to be split up per area)

Massive thanks for the work already done, and thanks in advance for the above.

Kind regards, galvinpaddy

[nilem]

Hi Galvinpaddy,
see attachment

[galvinpaddy]

Good monring Nilem,

When i copy the code to my active worksheet (with correct info etc) and run i receive a runtime error '13' type mismatch, with the following line in yellow

Please Login or Register  to view this content.

[nilem]

Hi Galvinpaddy (we have already noon)
Can you show this "...my active worksheet (with correct info etc)..."

[galvinpaddy]

Hi Nilem,

Apologies for the delay, had to remove all conditional formats and cell colours to reduce file size

All formulas have been replaced with values also, but the data is set out exactly as it is entered.

Kind regards

[nilem]

Error occurs because the macro loops through all the sheets in the workbook. All you need is some sheets to calculate the time. What are these sheets?

[galvinpaddy]

Hi Nilem,

Im not sure i understand, sorry

The times are in each of the sheets in Column Y (column X contains the figure but in HH:MM format, & Y is the numeric value)
The last code you provided on your sheet worked great, thats the result i would liek to see, but reading data from all of the tabs on my WC document.

[nilem]

try

Please Login or Register  to view this content.

[galvinpaddy]

OK.

Now solved i believe, Nilem, you are indeed a god send!!! thank-you very much indeed for your help, very much appreciated!!
Kind regards

galvinpaddy