Summing values from various sheets in a file based on employee id

[sivasankar]

Hi,

Please find the following data of 12 sheets (12 months) and i have named each sheet like jan, feb, mar, .....& Dec.


[TABLE="width: 1410"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1308"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]31[/TD]
[TD="colspan: 7"]Gross Salary per Month[/TD]
[TD="colspan: 7"]Earnings per Month[/TD]
[TD="colspan: 4"]Deductions[/TD]
[TD]Net Salary[/TD]
[/TR]
[TR]
[TD]Emp Code[/TD]
[TD]Location[/TD]
[TD]NAME OF THE EMPLOYEE[/TD]
[TD]Present Days[/TD]
[TD]Basic[/TD]
[TD]HRA[/TD]
[TD]Con Allow[/TD]
[TD]Medical Reimb[/TD]
[TD]Uniform Allow[/TD]
[TD]Spl Allow[/TD]
[TD]Total[/TD]
[TD]Basic[/TD]
[TD]HRA[/TD]
[TD]Con. Allow[/TD]
[TD]Medical Reimb[/TD]
[TD]Uniform Allow[/TD]
[TD]Spl Allow[/TD]
[TD]Total[/TD]
[TD]PF[/TD]
[TD]PT[/TD]
[TD]TDS[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]100002[/TD]
[TD]Banglore[/TD]
[TD]Praveen[/TD]
[TD]31[/TD]
[TD]15363[/TD]
[TD]7682[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]1000[/TD]
[TD]2831[/TD]
[TD]28926[/TD]
[TD]15363[/TD]
[TD]7682[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]1000[/TD]
[TD]2831[/TD]
[TD]28926[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]980[/TD]
[TD]27946[/TD]
[/TR]
[TR]
[TD]100003[/TD]
[TD]Hyderabad [/TD]
[TD]Siva[/TD]
[TD]30[/TD]
[TD]85338[/TD]
[TD]42669[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]2500[/TD]
[TD]25321[/TD]
[TD]157878[/TD]
[TD]82585[/TD]
[TD]41293[/TD]
[TD]774[/TD]
[TD]1210[/TD]
[TD]2419[/TD]
[TD]24504[/TD]
[TD]152785[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]980[/TD]
[TD]151805[/TD]
[/TR]
[TR]
[TD]100004[/TD]
[TD]Hyderabad [/TD]
[TD]sankar[/TD]
[TD]25[/TD]
[TD]57390[/TD]
[TD]28695[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]2500[/TD]
[TD]15022[/TD]
[TD]105657[/TD]
[TD]46282[/TD]
[TD]23141[/TD]
[TD]645[/TD]
[TD]1008[/TD]
[TD]2016[/TD]
[TD]12115[/TD]
[TD]85207[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]10000[/TD]
[TD]10980[/TD]
[TD]74227[/TD]
[/TR]
[TR]
[TD]100005[/TD]
[TD]Banglore[/TD]
[TD]Rao[/TD]
[TD]20[/TD]
[TD]102405[/TD]
[TD]51203[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]2500[/TD]
[TD]31612[/TD]
[TD]189770[/TD]
[TD]66068[/TD]
[TD]33034[/TD]
[TD]516[/TD]
[TD]806[/TD]
[TD]1613[/TD]
[TD]20395[/TD]
[TD]122432[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]57068[/TD]
[TD]58048[/TD]
[TD]64384[/TD]
[/TR]
[TR]
[TD]100006[/TD]
[TD]Banglore[/TD]
[TD]Kumar[/TD]
[TD]31[/TD]
[TD]15959[/TD]
[TD]7980[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]1000[/TD]
[TD]3486[/TD]
[TD]30475[/TD]
[TD]15959[/TD]
[TD]7980[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]1000[/TD]
[TD]3486[/TD]
[TD]30475[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]980[/TD]
[TD]29495[/TD]
[/TR]
[TR]
[TD]100007[/TD]
[TD]Indore[/TD]
[TD]hari[/TD]
[TD]31[/TD]
[TD]57390[/TD]
[TD]28695[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]2500[/TD]
[TD]15022[/TD]
[TD]105657[/TD]
[TD]57390[/TD]
[TD]28695[/TD]
[TD]800[/TD]
[TD]1250[/TD]
[TD]2500[/TD]
[TD]15022[/TD]
[TD]105657[/TD]
[TD]780[/TD]
[TD]200[/TD]
[TD]8696[/TD]
[TD]9676[/TD]
[TD]95981[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"][/TD]
[TD="colspan: 7"][/TD]
[TD="colspan: 6"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And i am trying to add the values based on emp code from all sheets into another sheet. I have tried with VLOOKUP and its working if the emp code is exists in those months / sheets. if emp code is not there it is giving error #N/A.

the code which i have tried is

=+VLOOKUP(B7,apr,26,FALSE)+VLOOKUP(B7,may,26,FALSE)+VLOOKUP(B7,jun,26,FALSE)+VLOOKUP(B7,Jul,26,FALSE)+VLOOKUP(B7,Aug,26,FALSE)+VLOOKUP(B7,Sep,26,FALSE)+VLOOKUP(B7,Oct,26,FALSE)+VLOOKUP(B7,Nov,26,FALSE)+VLOOKUP(B7,Dec,26,FALSE)+VLOOKUP(B7,Jan,26,FALSE)+VLOOKUP(B7,Feb,26,FALSE)+VLOOKUP(B7,Mar,26,FALSE)

please help me in simplifying the code.

thanks in Advance.

Siva Sankar

 

[Aladin Akyurek]

Have a look at: http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[sivasankar]

Thanks a Lot. I have tried Option 1 and its working.

 

[Aladin Akyurek]

Thanks a Lot. I have tried Option 1 and its working.

You are welcome. That's great to hear.