Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not a clue of which function to use!
My company keeps a database of attendance by employee. Not being a very user friendly database we copy and paste this data (along with lots of useless information) into Excel using a tab for each weeks attendance. I need to find a way to show how many weeks each employee has attended. Somehow I need to search each worksheet for the persons name and the hours worked that week. We also have a very high staff turnover so some names only appear for a few weeks and then disappear but I still need to be able to show that they attended for however many weeks. Does any of this make sense and does anyone have any suggestions for how to extract the information, please? -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947 View this thread: http://www.excelforum.com/showthread...hreadid=531991 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not a clue of which function to use!
From a previous posting: reply by Peo Sjoblom
If you only have one criteria like in a sumif and either sum the same range or another range you can use this: First you need to create a list with ALL (not like in 3-D excel first and last) sheet names or if they have the same aplha name plus numbers you can do it as well. 1. if they all have different names and you put all sheet names in range H1:H20 - range should be equal to number of sheets and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1 then you can use this =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A1 00"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B10 0"))) if the names are identical text plus different numbers like Sheet1, Sheet2 amd so one then you can use =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet" &ROW(INDIRECT("1:20"))&"'!B1:B100"))) if you need more criteria and more ranges may I recommend Morefun by Laurent Longre It's an excellent add-in that has some nice 3-D features http://longre.free.fr/english/ here's a description in English http://www.rhdatasolutions.com/morefunc/ Regards, Peo Sjoblom "y_not" wrote: My company keeps a database of attendance by employee. Not being a very user friendly database we copy and paste this data (along with lots of useless information) into Excel using a tab for each weeks attendance. I need to find a way to show how many weeks each employee has attended. Somehow I need to search each worksheet for the persons name and the hours worked that week. We also have a very high staff turnover so some names only appear for a few weeks and then disappear but I still need to be able to show that they attended for however many weeks. Does any of this make sense and does anyone have any suggestions for how to extract the information, please? -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947 View this thread: http://www.excelforum.com/showthread...hreadid=531991 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |