create employee attendance annual summary sheet

Hi Team,

I am having employee attendance sheet which contains 12 sheets with different months, what I need is I have created a new summary sheet in that I need to get how much leave/sickleave/comp off has been taken in a year by the employee on the summary sheet. I have attached spread sheet for your reference.

Kindly do the needful at the earliest.

Regards,
Girish

Hi Team,

Please find the attached updated spread sheet and I mentioned comments on the spread sheet that what I needed. Please let us know if you need further information and kindly assist me at the earliest. Thanks.

Regards,
Girish

Here's one solution... will this work for you (i.e see attachment)

Hi Team,

Thanks for working on this, but I can't copy all the data in one sheet since I have more than thousand employees in a sheet and all r in different order (ex: employee 1 will be in A4 on Jan and it will be in different column on Feb sheet). Please check and advise. Thanks.

Regards,
Girish

sorry its not the different column... its a different row

Hello Giri: The solution I provided you DID NOT rely on the order of the employees. I utilized the "sumif" formula which allows the information to be in any row on any sheet. However I have modified the solution to allow each month to be on a separate sheet. ... please see attachment.

Updated formula for "P"

Hi Team,

Thanks for your help, i need help on last one thing, how you have created the drop down on summary sheet. I know it can be done using data validation but how the data will reflect as soon as we select month from the drop down. please advise.

Regards
Girish

one more question please....

we have different employee counts on each sheet for example on Jan 4 employees, on Feb 6 employees, Mar 8 employees.....etc. since employees joining on every month. how to insert all the employees in "ALL" sheet and in "summary" sheet. please help. Thanks.

Regards,
Girish

To add a new employee to the "All" page just copy a row that already is on the "All" page and then just change the name of the employee to the new one.

Hello Giri:
I'm not sure I understand what your problem is with the Month drop down list ... could you please explain with an example ?

Hi Team,

Everything works fine but on summary you have created a drop down using data validation and when u select the month u will get the information on the table using the formula below but when I tried to create the same table on sheet 1 I am getting error. Please check and assist me, I have attached the spread sheet and also explain me in brief on how u have created the drop down and inserted the formula on summary sheet. Thanks.

Formula used by u in summary sheet.

=SUMIF(INDIRECT($C$7 & "!$A:$A"),$B9,INDIRECT($C$7 & "!$B:$B"))


Regards,
Girish

Hello Giri:

My Formula : =SUMIF(INDIRECT($C$7 & "!$A:$A"),$B9,INDIRECT($C$7 & "!$B:$B"))

Your Formula: =SUMIF(INDIRECT($E$5 & "ALL!$A:$A"),$D7,INDIRECT(Sheet1!$E$5 & "ALL!$B:$B"))

in my formula the name of the sheet comes from $C$7 ... so $C$7 & "!$A:$A" equals All!$A:$A

in your formula the name of the sheet is in $E$5 ... so $E$5 & "ALL!$A:$A" equals AllAll!$A:$A

Since there is no sheet called "AllAll" your formula fails. Both "INDIRECT" in the formula fail for this reason.


By-the-way ... my name is Nimrod , thx

To add/edit the drop down list please follow these instructions

1. Select the cell that contains the drop down list
2. Select/click the "Data" tab on the toolbar
3. click the data validation icon
4. click "data validation" on the drop down men
5. In Data Validation pop window select the "Settings" Tab
6. Edit the "Source" list, making sure to include a comma between each item in list

Hi Loganeb,

Thanks for giving the easiest way by creating pivot table. yes i am looking for what u did. please help me on explaining how u did that pivot table at the earliest since its quiet urgent. Thanks.

Regards,
Girish

Hi Loganeb,

Thank you for the easiest way, please assist me on how to do that using pivot table.

Hi Team,

I am waiting for your assistance on the above request. please assist me on how to do that?

Hi Team,

Please assist me on how to do the above given easiest way....please