• 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

    Hui...

  • 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

Alina_T

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!
 

Attachments

  • work vacation calendar 2016.xlsx
    63.9 KB · Views: 7
In C7 try formula below (copy across)
Code:
=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.
 

Attachments

  • alinaT-navic-42730.xlsx
    485.1 KB · Views: 8
Back
Top