[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:

 

[hasanimam85]

HI Shamima

Thanks a gazillion for the help. I have been trying to add employees but there is some error, and the data is not captured/shown properly. Is it possible for you to add a total of 15 or 20 employees? You can use dummy names which I will edit later on.

Also, I would like to ask if we can make a column which contains pending comp offs for each employee. For e.g., I have 4 pending comp offs as of now, if I take 1 during the week, 1 shall be deducted from my pending comp offs, and if I work on Saturday, 1 should be added in my pending comp offs cell.

Please let me know if any payment or any support is required. I will be more than happy to help.