Yearly Attendance Tracker

  • Hello, Experts!


    Back again, this time with a yearly attendance tracker issue I'm unable to solve with my limited knowledge.


    The template itself is something I got off the interweb, and I was able to add the coloumns in the beginning and have the lookups work (I think).


    Couloumns NU:OF is what I cant seem to get my head around, no matter how much I try fiddling with the Offset formula.


    What I would like the sheet to do is:


    give me the total monthly leaves in coloumn NU, total annual leaves in NV and the monthly break-up in coloumns NW:OF (the leave codes are in coloumn OI)


    The holidays sheet and its functionality is something that I don't need, but I had to leave it in because it crashed the entire thing when i tried removing it.


    What would be great, too, is if the experts could simplify the Offset formula for me, if it isn't too much to ask for. I get the basics, as in it looks x rows down and across, and returns the value there, but I fail to understand how that works when the calendar changes to the next month.


    I did try searching on here as well as other places for a similar issue, and did run across one where the author was having issues inserting coloumns in the beginning, but that was about it, so I'm here once again.


    Hope someone can shed some light on the situation.

  • Hello,


    In your cell NU8, your formula should be :


    Code
    1. =COUNTIF(OFFSET(A8,0,6,1,31),"P")


    Since the Offset() functions requires, in this case, all five elements ...


    Debra has an excellent explanation for this function :


    https://www.contextures.com/exceloffsetfunction.html


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim,


    Thank you, once again, for your reply.


    The link you have shared has helped me learn and implement the Offset as well as the Index formulae.


    The learning never stops, thank you, OZGrid!

  • Glad you could fix your problem ...:wink:


    Debra has a truly excellent reference site ...!!!


    Thanks for your Thanks ... AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)