Creating a leave tracker

I have a current month calendar in sheet 1. In sheet 2 i have the list of employees in column A and in row 2 from Column B to Column AF i have dates of the current month marked. If for example in the current date column of Sheet 2 i mark "L"(Leave) for any employee i need the name of the employee to be displayed under the current date in Sheet 1 with Green color text. If i mark as "A"(Absent) for any employee i need the name of the employee to be displayed under the current date in Sheet 1 with Red color text.

Can any one please suggest any formula or process to achieve this. Any help in this regard is highly appreciated.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

In the meantime you might want to check out this thread from a few days ago:

https://www.excelforum.com/excel-gen...-calendar.html

and take a look at some of the attachments linked to there.

Hope this helps.

Pete

Pete, Thank u for your response.

I have attached the sheet as per the procedure you have mentioned. In the sheet May 2017, under the Column P i have marked as L, WFH and A for some Employees.
I want the employee name along with the comment to be displayed in the calendar sheet under the same date. (Example : Employee6(L); Employee9(WFH) etc).
Request you to suggest on how to achieve this. The format can be modified if required and if you can suggest a better way of making the sheet work, i would be delighted.

Once again thanks a lot for your help.

Regards
Kiran.

I would suggest that you just use one sheet to record all leave taken. This can be similar to the Booked sheet in the file that you downloaded, with an extra column for Leave_type, and you can enter the bookings in any order for any individual member of staff. You can then generate the monthly sheets from that basic data, as well as displaying the Calendar in the form that you show. Instead of having 12 individual monthly sheets, you can have a single sheet on which you can select the month and year from two drop-downs, so your file would be much more compact, and would allow you to use it from one year to the next.

Hope this helps.

Pete

Pete, Thanks a lot for your guidance and help.

I was able to create one sheet to record all leave taken and get the name of the employees to display in the Calendar. It simplified the work.

Appreciate your help.
Kiran.

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete