counting data across worksheets

[Guest]

I need to do the following:-

I have 12 worksheets (one for each month) which contain information on staff
members, when they were on duty, when they were on holiday etc.

I have successfully used COUNTIF to work out how many days holiday each
staff member has had at the end of each month (defined by "L"). using

=COUNTIF(D4:AH4, "L")

then dragging the formula down for each staff member.

This has been successful, however some staff members have now left their
job, and the roles have been filled by someone else. Therefore the
information across the worksheets is not consistent.

I want to have a summary for each staff member on a separate worksheet, and
have a running total with how many days leave they have left by counting the
times they have been off and subtracting that from their entitlement (which
is 44 days).

Is there any way i can do an IF function or some sort of lookup reference to
enable me to have a year end total of holiday leave for each individual staff
member??

I'm sorry if this is very confusing, and any help is greatly appreciated!

 

[Aqib Rizvi]

Instead of IF, try COUNTIF, that will give you totals by the person.
AQIB RIZVI

 

[Guest]

how would you suggest i do this?

what i need to do is

eg

January

A B C D E F G
H I J
1 1 Mr A 12 12 L 8 16 L L L
4
2 2 Mr B L 12 12 L L 8 16 L
4
3 3 Mr C 8 16 12 12 8 16 L 12 12 L
2

Column A is staff number, B is the persons name, 12 12 and 8 16 are shift
patterns, L is Leave.

Column J is a COUNTIF formula to count the amount of L's across the row.

Imagine February looks like this

February

A B C D E F G
H I J
1 1 Mr A 12 12 L 8 16 L L L
4
2 3 Mr C L 12 12 L L 8 16 L
4
3 2 Mr B 8 16 12 12 8 16 L 12 12 L
2

Mr A is still in the same job, but B and C have changed. This occurs quite
a lot throughout the year, where the staff members take over a certain job
temporarily, or leave their post. According to the data above, i need to
have a final result of

Mr A 8
Mr B 6
Mr C 6 days leave

I don't know if COUNTIF would let me do this. I think i need to search the
page for "if January B1=Mr A then count January J1" etc is there a formula
that would allow this?

 

[Don Guillett]

If you want to sum col J for each worker no matter where he is in col B,
then I think I would use a for/each macro something like

Sub getsumforeachemployee()
For Each n In Sheets("summary").Range("a2:a4")
ms = 0
For i = 2 To Sheets.Count
ms = ms + Sheets(i).Columns(2).Find(n).Offset(, 8)
Next i
'MsgBox ms
n.Offset(, 1) = ms
Next n
End Sub

 

[Guest]

don,

I am not too familiar with macros, so was wondering if you could guide me
through the one you suggested.

I was also wondering, would this allow me to compile the data at the end of
the year? eg, if Mr A has a total leave of 5 days for jan, 2 for feb, 7 for
mar, 8 for apr etc. all on separate worksheets, in different rows on some of
the worksheets. The numbers that i need to sum are the COUNTIF values that i
have in column J (for Mr A that is cell J4). I want a final value that shows
how many days Mr A had during the year, regardless of what job he did. So,
J4 January + J5 February + J4 March etc (depending on whether he had changed
jobs that month - changed rows in that worksheet).

I am sorry for any confusion, I have tried to explain as best i can without
going into too much detail!

Thanks for any help you can give.

 

[Don Guillett]

Assuming you have a worksheet named "Summary" with the employees names in
col A >copy the code into a macro module(alt f11 to get there)>try it.
After all else fails, send your workbook to my address below