[Solved] Issue in leave tracker

[hasanimam85]

Dear Team

Goodmorning. Thanks for doing all the excellent work. I followed the step-by-step process for creating leave tracker, however, on the step where we have to paste the large formulae, the total number of leaves are not getting calculated i.e. year summary.

I have attached my file here. Can you please have a look and fix the issue or advise me how to fix?

Also, I would like to know how to add more employees in the sheet, I am looking for around 36 total employees. Is this possible?

Thanks a lot again

 

[shamimarita]

Dear Team

Goodmorning. Thanks for doing all the excellent work. I followed the step-by-step process for creating leave tracker, however, on the step where we have to paste the large formulae, the total number of leaves are not getting calculated i.e. year summary.

I have attached my file here. Can you please have a look and fix the issue or advise me how to fix?

Also, I would like to know how to add more employees in the sheet, I am looking for around 36 total employees. Is this possible?

Thanks a lot again

Hello Hasanimam85,

The range of your formula was incorrect that's why it wasn't getting any values. I updated the formula and also added a New Employee. You can add as many employees as you want within Excel limits. Please remember to update the formula according to your dataset.

The initial formula was:

=IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B10,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B10,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B10,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B10,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B10,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B10,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B10,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B10,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B10,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B10,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B10,Dec!$C$9:$C$13,0)),0)



As I added a new employee I changed the ranges of the formula in the Summary sheet.

=IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($B15,Jan!$C$9:$C$14,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$14,MATCH($B15,Feb!$C$9:$C$14,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$14,MATCH($B15,Mar!$C$9:$C$14,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$14,MATCH($B15,Apr!$C$9:$C$14,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$14,MATCH($B15,May!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$14,MATCH($B15,Jun!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$14,MATCH($B15,Jul!$C$9:$C$14,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$14,MATCH($B15,Aug!$C$9:$C$14,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$14,MATCH($B15,Sep!$C$9:$C$14,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$14,MATCH($B15,Oct!$C$9:$C$14,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$14,MATCH($B15,Nov!$C$9:$C$14,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$14,MATCH($B15,Dec!$C$9:$C$14,0)),0)



To add new employees to the Months sheet (Jan, Feb...) follow the steps given below:

• Insert Employee Name C14 cell.
• Select range D13:AD13 then drag it down
• Update the range of Total leaves in AJ8:AD8 columns.

Here is the updated Excel file: