|
counting data across worksheets
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! |
counting data across worksheets
Instead of IF, try COUNTIF, that will give you totals by the person.
AQIB RIZVI |
counting data across worksheets
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? |
counting data across worksheets
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "franki_85" wrote in message ... 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? |
counting data across worksheets
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "franki_85" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "franki_85" wrote in message ... 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? |
| All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com