Calendar issue trying to get holidays and birthdays to update

[AlanStrobel]

Hi Mr. Excel,

I got a issue trying to get holidays and birthdays to update from a sheet called Memo to a sheet called Calendar. I got the calendar to work the way I want it but cannot figure out how to make the holidays or birthdays listed on another sheet to show on the calendar or change based on the month selected. Is there a way to send the whole file so you can see all the code.

Thanks

Alan S.

 

[awoohaw]

you can share a link to it with a dropbox or onedrive account that you have. But, many people are reluctant to open random links.
Are you doing this with VBA or using formulas?

Mr. Excel has a tool called xl2bb add in (link below) ... you could probably post with xl2bb (or post a table - not an image) a portion of your birthday list and a portion of your calendar worksheet(s). It could be enough to get a good reply. If you have vba, you can post the vba with it as well (use the "VBA" quoting tool that is above each post composing section).

 

[AlanStrobel]

Hi

This code is in Calendar1 image
A1 code =UPPER(TEXT(DATE(S4,S3,1),"MMMM YYYY")) This is the month and year showing on the calendar, which this all changes from the Control Area box
C3 code =Q52
each of the cells that have a day would have similar code

This is calendar2 image sheet with Holidays and Birthdays.
Code in Cells in column A this was just an attempt if there is a better way I'm good with changing it. =IF(B2=1,$M$2,IF(B2=2,$M$3,IF(B2=3,$M$4,IF(B2=4,$M$5,IF(B2=5,$M$6,IF(B2=6,$M$7,IF(B2=7,$M$8,IF(B2=8,$M$9,IF(B2=9,$M$10,IF(B2=10,$M$11,IF(B2=11,$M$12,IF(B2=12,$M$13,""))))))))))))

Want to make it where I take the sheet with birthdays and have them show up on the day it correlates to on the calendar and change\update when I change the month or year.

 

[awoohaw]

Is there a reason why you don't want to help the forum help you?
Please post using the xl2bb add in, a table posting, or a send a share-able file link.

You can change your data to protect anonymity and data disclosure.

You're asking the forum to completely recreate your application and there can be typos and errors.

Thanks in advance.

 

[AlanStrobel]

Calendar - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	JANUARY 2024
2	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday			Control Area
3			1	New Year's Day	2		3		4		5		6			Month			1
4																Year			2024
5
6
7
8
9	7		8		9		10		11		12		13
10
11
12
13
14
15	14		15		16		17		18		19		20
16
17
18
19
20
21	21		22		23		24		25		26		27
22
23
24
25
26
27	28		29		30		31
28
29
30
31
32
33
34
35
36
37
38
39
40
41																December-2023
42																Su	M	Tu	W	Th	F	Sa
43																					1	2
44																3	4	5	6	7	8	9
45																10	11	12	13	14	15	16
46																17	18	19	20	21	22	23
47																24	25	26	27	28	29	30
48																31
49
50																January-2024
51																Su	M	Tu	W	Th	F	Sa
52																	1	2	3	4	5	6
53																7	8	9	10	11	12	13
54																14	15	16	17	18	19	20
55																21	22	23	24	25	26	27
56																28	29	30	31
57
58
59																February-2024
60																Su	M	Tu	W	Th	F	Sa
61																				1	2	3
62																4	5	6	7	8	9	10
63																11	12	13	14	15	16	17
64																18	19	20	21	22	23	24
65																25	26	27	28	29
66
67
Calendar

Cell Formulas
Range	Formula
A1	A1	=UPPER(TEXT(DATE(S4,S3,1),"MMMM YYYY"))
A2,M2,K2,I2,G2,E2,C2	A2	=TEXT(A9,"dddd")
A3	A3	=P52
B3	B3	=IF(A3="","",XLOOKUP(S3&Memo!M2,Memo!A2:A15&Memo!B2:B15,Memo!C2:C15,""))
C3	C3	=Q52
D3	D3	=IF(C3="","",IF(C3=Q52,XLOOKUP(S3&Memo!M2,Memo!A2:A15&Memo!B2:B15,Memo!C2:C15,101)))
E3	E3	=R52
G3	G3	=S52
I3	I3	=T52
K3	K3	=U52
M3	M3	=V52
A9	A9	=P53
C9	C9	=Q53
E9	E9	=R53
G9	G9	=S53
I9	I9	=T53
K9	K9	=U53
M9	M9	=V53
A15	A15	=P54
C15	C15	=Q54
E15	E15	=R54
G15	G15	=S54
I15	I15	=T54
K15	K15	=U54
M15	M15	=V54
A21	A21	=$P55
C21	C21	=Q55
E21	E21	=R55
G21	G21	=S55
I21	I21	=T55
K21	K21	=U55
M21	M21	=V55
A27	A27	=P56
C27	C27	=Q56
E27	E27	=R56
G27	G27	=S56
I27	I27	=T56
K27	K27	=U56
M27	M27	=V56
A33	A33	=P57
C33	C33	=Q57
P41	P41	=DATE(YEAR(P50-5),MONTH(P50-5),1)
P42,P60,P51	P42	=IF($W$6,"M","Su")
Q42,Q60,Q51	Q42	=IF($W$6,"Tu","M")
R42,R60,R51	R42	=IF($W$6,"W","Tu")
S42,S60,S51	S42	=IF($W$6,"Th","W")
T42,T60,T51	T42	=IF($W$6,"F","Th")
U42,U60,U51	U42	=IF($W$6,"Sa","F")
V42,V60,V51	V42	=IF($W$6,"Su","Sa")
P43:V48	P43	=IF(MONTH($P$41)<>MONTH($P$41-WEEKDAY($P$41,$W$6+1)+(ROW(P43)-ROW($P$43))*7+(COLUMN(P43)-COLUMN($P$43)+1)),"",$P$41-WEEKDAY($P$41,$W$6+1)+(ROW(P43)-ROW($P$43))*7+(COLUMN(P43)-COLUMN($P$43)+1))
P50	P50	=DATE(S4,S3,1)
P52:V57	P52	=IF(MONTH($P$50)<>MONTH($P$50-WEEKDAY($P$50,$W$6+1)+(ROW(P52)-ROW($P$52))*7+(COLUMN(P52)-COLUMN($P$52)+1)),"",$P$50-WEEKDAY($P$50,$W$6+1)+(ROW(P52)-ROW($P$52))*7+(COLUMN(P52)-COLUMN($P$52)+1))
P59	P59	=DATE(YEAR(P50+35),MONTH(P50+35),1)
P61:V66	P61	=IF(MONTH($P$59)<>MONTH($P$59-WEEKDAY($P$59,$W$6+1)+(ROW(P61)-ROW($P$61))*7+(COLUMN(P61)-COLUMN($P$61)+1)),"",$P$59-WEEKDAY($P$59,$W$6+1)+(ROW(P61)-ROW($P$61))*7+(COLUMN(P61)-COLUMN($P$61)+1))

Named Ranges
Name	Refers To	Cells
_10	=Calendar!$Q$45:$V$45	Q45
_17	=Calendar!$Q$46:$V$46	Q46
_24	=Calendar!$Q$47:$V$47	Q47
_3	=Calendar!$Q$44:$V$44	Q44

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A27:B32	Expression	=$A$27=""	text	YES
C27:D32	Expression	=$C$27=""	text	YES
E27:F32	Expression	=$E$27=""	text	YES
I27:J32	Expression	=$I$27=""	text	YES
G27:H32	Expression	=$G$27=""	text	YES
M27:N32	Expression	=$M$27=""	text	YES
K27:L32	Expression	=$K$27=""	text	YES
C33:D38	Expression	=$C$33=""	text	YES
A33:B38	Expression	=$A$33=""	text	YES
K3:L8	Expression	=$K$3=""	text	YES
I3:J8	Expression	=$I$3=""	text	YES
G3:H8	Expression	=$G$3=""	text	YES
E3:F8	Expression	=$E$3=""	text	YES
C3:D8	Expression	=$C$3=""	text	YES
A3:B8	Expression	=$A$3=""	text	YES

 

[AlanStrobel]

Calendar - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	Day	Event									Control
2	1	1	New Year's Day									January	1
3	1	19	1 Birthday									February	2
4	2	10	2 Birthday									March	3
5	2	14	3 Birthday									April	4
6	2	14	4 Birthday									May	5
7	2	14	Valentines Day									June	6
8	5	27	Memorial Day									July	7
9	7	4	Independace Day									August	8
10	9	2	Labor Day									September	9
11	9	14	24 Birthday									October	10
12	9	14	25 Birthday									November	11
13	11	11	Veteran's Day									December	12
14	11	28	Thanksgiving
15	12	25	Christmas
Memo

 

[awoohaw]

Thanks for posting as an xl2bb! I should have also asked what version of excel do you use? If you use a version that has the FILTER, LET, and/or other LAMBDA functions this is probably a lot easier than with older versions. And also update your profile so the button displays your excel version to the forum.

 

[awoohaw]

I think this gets it for you. But, you must have the FILTER function in your version:

Paste this in only the top half of your workbook. Or just use this formula in cell B3 and copy to the other cells that are next to numbers.
(With your merged cells you may need to convert the array values into a list so it will fit in a merged cell. I loathe merged cells. To avoid that you can just format the cells with no visible grid lines.)

=IF(A3="","",FILTER(Memo!$C$2:$C$15,(Memo!$A$2:$A$15=MONTH(A3))*(Memo!$B$2:$B$15=DAY(A3)),""))

You can put a carriage return into a textjoin with text wrap formatting function if you want to have the merged cells:

=IF(G15="","",TEXTJOIN(CHAR(10),TRUE,FILTER(Memo!$C$2:$C$15,(Memo!$A$2:$A$15=MONTH(G15))*(Memo!$B$2:$B$15=DAY(G15)),"")))

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	FEBRUARY 2024
2	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
3									1		2		3
4
5
6
7
8
9	4		5		6		7		8		9		10	2 Birthday
10
11
12
13
14
15	11		12		13		14	3 Birthday	15		16		17
16								4 Birthday
17								Valentines Day
18
19
20
21	18		19		20		21		22		23		24
22
23
24
25
26
27	25		26		27		28		29
28
29
30
31
32
33
34
35
36
37
38
Calendar

Cell Formulas
Range	Formula
A1	A1	=UPPER(TEXT(DATE(S4,S3,1),"MMMM YYYY"))
A2,M2,K2,I2,G2,E2,C2	A2	=TEXT(A9,"dddd")
A3	A3	=P52
B3,D3,F3,H3,J3,L3,N3,M27,B27,D27,F27,H27,J27,B21,D21,F21,H21,J21,L21,N21,H15:H17,B15,D15,F15,J15,L15,N15,B9,D9,F9,H9,J9,L9,N9	B3	=IF(A3="","",FILTER(Memo!$C$2:$C$15,(Memo!$A$2:$A$15=MONTH(A3))*(Memo!$B$2:$B$15=DAY(A3)),""))
C3	C3	=Q52
E3	E3	=R52
G3	G3	=S52
I3	I3	=T52
K3	K3	=U52
M3	M3	=V52
A9	A9	=P53
C9	C9	=Q53
E9	E9	=R53
G9	G9	=S53
I9	I9	=T53
K9	K9	=U53
M9	M9	=V53
A15	A15	=P54
C15	C15	=Q54
E15	E15	=R54
G15	G15	=S54
I15	I15	=T54
K15	K15	=U54
M15	M15	=V54
A21	A21	=$P55
C21	C21	=Q55
E21	E21	=R55
G21	G21	=S55
I21	I21	=T55
K21	K21	=U55
M21	M21	=V55
A27	A27	=P56
C27	C27	=Q56
E27	E27	=R56
G27	G27	=S56
I27	I27	=T56
K27	K27	=U56
A33	A33	=P57
C33	C33	=Q57