Update calendar schedule with holidays

[kasper]

I'm a newbie who has grand ideas which may not be possible.
What I have is a yearly calendar with months across the top and days 1-31 down the left. My colleagues would enter PI for personal illness, or V for vacation and those days are totaled at the bottom of the column for each month. On a separate worksheet I have calculated the stat holidays from current year till forever (I would make a separate calendar worksheet each year).
What I want to do is on the calendar sheet, mark each of the stat holidays with an 'H'. Is this possible?

 

[Caribeiro77]

Can you post what you have so far, cause i'm a little confuse, isn't your calendar sheet the yearly calendar sheet, where you already put the PI's and the V's?

 

[kasper]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]pi[/TD]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Attendance 2015 (worksheet 1)

[TABLE="class: grid, width: 708"]
<tbody>[TR]
[TD]Holiday[/TD]
[TD]When[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Year's Day[/TD]
[TD]First day in January[/TD]
[TD="align: right"]1-Jan-15[/TD]
[TD]January[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2015[/TD]
[/TR]
[TR]
[TD]Family Day[/TD]
[TD]Third Monday in February[/TD]
[TD="align: right"]16-Feb-15[/TD]
[TD]February[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Good Friday[/TD]
[TD]Friday before Easter Monday[/TD]
[TD="align: right"]3-Apr-15[/TD]
[TD]April[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Easter Monday[/TD]
[TD]First Monday after first full moon in spring[/TD]
[TD="align: right"]5-Apr-15[/TD]
[TD]April[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Victoria Day[/TD]
[TD]Monday before May 25[/TD]
[TD="align: right"]18-May-15[/TD]
[TD]May[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Canada Day[/TD]
[TD]July 1[/TD]
[TD="align: right"]1-Jul-15[/TD]
[TD]July[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Civic Holiday[/TD]
[TD]First Monday in August[/TD]
[TD="align: right"]3-Aug-15[/TD]
[TD]August[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
holiday data (worksheet 2) - the date column dates are created by formula based on the year

I want the employee to enter their own values (sick days, or vacation days), but pre-populate holidays for them (if possible).

 

[kasper]

Sorry to be impatient, but if this isn't possible then I will just go ahead and distribute the workbook as-is. Can someone tell me if my request is doable please?

 

[Caribeiro77]

hi, to makes things easier i would advice you the following, create am helper column on sheet2 ( see exmple bellow)

Book1
	A	B	C	D	E	F	G	H
1	Holiday	When	Date
2	New Year's Day	First day in January	01-jan-15	January	1		January1	H
3	Family Day	Third Monday in February	16-fev-15	February	16		February16	H
4	Good Friday	Friday before Easter Monday	03-abr-15	April	3		April3	H
5	Easter Monday	First Monday after first full moon in spring	05-abr-15	April	6		April6	H
6	Victoria Day	Monday before May 25	18-05-2015	May	18		May18	H
7	Canada Day	July 1	01-jul-15	July	1		July1	H
8	Civic Holiday	First Monday in August	03-08-2015	August	3		August3	H
Sheet2

Cell Formulas
Range	Formula
G2	=D2&E2

drag down


And use a simple VLOOKUP on your attendance sheet( like bellow)

Book1
	A	B	C	D	E	F	G	H	I
1		January	February	March	April	May	June	July	August
2	1	H						H
3	2
4	3				H				H
5	4
6	5
7	6				H
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16		H
18	17
19	18					H
Attendance 2015

Cell Formulas
Range	Formula
B2	=IFNA(VLOOKUP(B$1&$A2,Sheet2!$G$2:$H$8,2,0),"")

drag Down/right

 

[kasper]

Fantastic! For some reason I couldn't get ifna to work - kept throwing #name? errors, so I used iferror and it worked great.
Just to further automate things, I set up named ranges for the holidays each year (year2015 = $G$2:$H$8). Is there a way to enter the name of the range somewhere on the Attendance sheet and refer to it in the formula? Would make it so much easier to update for future years.

 

[Caribeiro77]

I don't know if i understood your question, is it something like this:

	A	B	C	D	E	F	G	H	I	J
Holiday	When	Date	Year_2015	Year_2016
New Year's Day	First day in January	January	January1	H	January4	H
Family Day	Third Monday in February	February	February16	H	February11	H
Good Friday	Friday before Easter Monday	April	April3	H	April12	H
Easter Monday	First Monday after first full moon in spring	April	April6	H	April13	H
Victoria Day	Monday before May 25	May	May18	H	May14	H
Canada Day	July 1	July	July1	H	July15	H
Civic Holiday	First Monday in August	August	August3	H	August16	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01-jan-15[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]16-fev-15[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]03-abr-15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]05-abr-15[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18-05-2015[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]01-jul-15[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]03-08-2015[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

I manually typed dummy dates on sheet2 cells I2:I8 to create a named range (Year_2016)
Then on sheet attendance you just have to change the year on the formula:

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]January[/TD]
[TD="bgcolor: #FAFAFA"]February[/TD]
[TD="bgcolor: #FAFAFA"]March[/TD]
[TD="bgcolor: #FAFAFA"]April[/TD]
[TD="bgcolor: #FAFAFA"]May[/TD]
[TD="bgcolor: #FAFAFA"]June[/TD]
[TD="bgcolor: #FAFAFA"]July[/TD]
[TD="bgcolor: #FAFAFA"]August[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>

Attendance 2015​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IFNA(VLOOKUP(B$1&$A2,Year_2016,2,0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


if on this formula above you change 2016 to 2015 everything changes.....

Know to create named ranges before using this formula create this 2 named ranges on the name maneger box:

=OFFSET(Sheet2!$G$2;;;COUNTA(Sheet2!$G$2:$G$25);2)

=OFFSET(Sheet2!$I$2;;;COUNTA(Sheet2!$I$2:$I$25);2)

 

[kasper]

Sorry, guess I'm not the greatest at explaining myself, but you were on the right track anyway.
I was hoping to change the existing lookup formula to something more like this...
=IFERROR(VLOOKUP(D$3&$C4,"year"&$b$2,2,0),"")
("year"&$b$2 = year2015)
Where B2 on every year's Attendance sheet would be the current year. Then I wouldn't need to update all the formulas on each year's spreadsheet - I'd only have to change the year in B2 to have all the holidays updated for that sheet.

Excel 2012

	B	C	D	E	F

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084"]January[/TD]
[TD="bgcolor: #F4B084"]February[/TD]
[TD="bgcolor: #F4B084"]March[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]1[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(D$3&$C4,Sheet2!$G$2:$H$8,2,0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]