Calendar for Employees' Vacations tracking

New Contributor

my idea is to have an excel file that is made of 2 Sheets:
sheet1: is a template of a year calendar overview with an employee name field
sheet 2: is the List of Employees in the company (around 500 employees) next to each employee there is a range of cells (vacation balance of the employee) where i want to write the date of the vacation.

what i want to do: is when i add the name of a certain emplyee in the calendar sheet (sheet1) all the dates of vacations written next to his name in (sheet 2) will be reflected as the letter (U) in the calendar sheet in the right day cell.
i have advanced skills in excel but i think this is somehow way over my range.
please help!
thanks in advance

7 Replies


Employee time sheet (weekly, monthly, yearly)

Time Sheet


If this Information dont helps, please ... Give more detailed information about your project.

It is best to insert a file (without sensitive data), where you describe your project step by step on the basis of this file.

( Drag and drop here or browse files to attach

Maximum size: 71 MB • Maximum attachments allowed: 5   )

Inform us about your computer environment, Excel version, operating system, storage medium (hard drive, OneDrive, Sharepoint, tec.).

In this way you can get a solution proposal that is tailored to you much faster and more precisely,

and you save the helpers a lot of time in that they no longer need to guess what-where-how your plan is. Welcome to your Excel discussion space!



Here you can get help to get on with your project / plan / topic.

But when it comes to commissioned work, you should hire someone to do it for you.


Hope I was able to help you with this info.

Thank you for your understanding and patience



I know I don't know anything (Socrates)


Hey Niko,
Thanks a lot for your answer!
unfortunately i don't have these information about the PC and the other things that you asked me for, and i cannot send the file because we have here in our company very strickt policies about sending intern template outside the company -_- and the IT has already blocked the ability to upload files online .
however what you sent me is somehow similar to what i am trying to build..

the file i am trying to do has the sheet you send which i will call "Template sheet1" and it should import the attendance infromation from another sheet (lets call it "Employees list sheet2") in the same excel file
so depending on the Employee's name, once i insert an employee's name in the concernd field (Sheet1), the attendance information (which is already inputted in Sheet2) will show up accordingly in sheet1..
anyway, i need it only for Vacations not full attendance details.
i have already watched so many videos and i already build the calendar template, but i am still trying to get the infos in sheet2 to sheet 1 in the correct days ..
thanks alot for your suppor and understanding
best regards


I can imagine what you mean, but in order to be able to offer you an exact solution, as I have written to you, some preconditions would be necessary.

Everything is possible in the world of Excel ... and you can do just as much wrong if you don't know how-where-what-when in advance. Welcome to your Excel discussion space!

Many things can be solved in Excel, with many possible paths, which is why very precise specifications are required.


However, here are two possible approaches that might help.

COUNTIF function

COUNTIFS function


VLOOKUP function

LOOKUP function

XLOOKUP function


Two files are attached with examples (I'm not sure you can download them).


Hope I was able to help you with this info.

Thank you for your understanding and patience



I know I don't know anything (Socrates)

Hello @NikolinoDE
thanks so much for the files you've sent
I am going on with this little project i am doing here,
i will keep on searching untill i find the answer and will definitally write you about it .
wish you a nice day and stay safe


I am pleased that these files, links and information have helped you in your projects.

I wish you continued success with Excel.



I know I don't know anything (Socrates)

Hello @NikolinoDE 


Hope my message finds you well
i haven't found out the way to link the two sheets i told you about earlier.
but i found this template in excel. it is almost what i want..

in the main sheet (Calendar Overview), the vacation days are reflected as colors., and i wanted them to be reflected as a Letter. 
So I will add a row under each month and in this row - under the vacation date - i want to have a letter instead of a color
for Example (V) for Vacation instead of having the day colored with blue, the V letter should apper under the date when i add a vacation date in the sheet (Employees-Attendance-Tracker) ..
may you please help ?

thanks a lot in advance 



Linking two worksheets in one workbook, here is a link to it:

How to Combine Two (Or More) Excel Spreadsheets Together


To make text stand out in color I recommend conditional formatting, here is a link with an example file too.

Use conditional formatting to highlight information


I hope the information/links will help you further.