• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split vacation calendar -> one vacation calendar with different people on the job


New Member
I have 4 sheets in excel of a vacation calendar (3 months only) with some people. Number of people is different from sheet to sheet.
I want to make one single calendar for that year - I have to look for the person and to put that value founded on intersection of the date with the name of the person.
Thank you in advance!



Active Member
In C7 try formula below (copy across)
=IF(MONTH(C$5)<=3,IFERROR(INDEX('Trim 1'!$C$7:$CO$30,MATCH($B7,'Trim 1'!$B$7:$B$30,0),MATCH(C$5,'Trim 1'!$C$5:$CO$5,0)),0),IF(AND(MONTH(C$5)>3,MONTH(C$5)<=6),IFERROR(INDEX('Trim 2'!$C$7:$CO$30,MATCH($B7,'Trim 2'!$B$7:$B$30,0),MATCH(C$5,'Trim 2'!$C$5:$CO$5,0)),0),IF(AND(MONTH(C$5)>6,MONTH(C$5)<=9),IFERROR(INDEX('Trim 3'!$C$7:$CP$30,MATCH($B7,'Trim 3'!$B$7:$B$30,0),MATCH(C$5,'Trim 3'!$C$5:$CP$5,0)),0),IF(AND(MONTH(C$5)>9,MONTH(C$5)<=12),IFERROR(INDEX('Trim 4'!$C$7:$CP$30,MATCH($B7,'Trim 4'!$B$7:$B$30,0),MATCH(C$5,'Trim 4'!$C$5:$CP$5,0)),0),0))))
But you have to meet some requirements in each quarter.
If you want to hide zero, then it uses the IF function
See attached file

btw: At the link you have a complete tutorial for a record holiday season employees. The tutorial is in Croatian but if you are interested try in translate it using Google Translator. You have a download file there.