Not a clue of which function to use!

[y_not]

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?

 

[Guest]

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:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))


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