filling cells with using values in helper cell

[jbrown021286]

Any year calendar (single month per tab)1
	A	B	C	D	E	F	G	H	I
1		January 2024
2		SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3		31	1	2	3	4	5	6
4									1
5									1
6
7
8		7	8	9	10	11	12	13
9									2
10									2
11
12
13		14	15	16	17	18	19	20
14									3
15									3
16
17
18		21	22	23	24	25	26	27
19									1
20									4
21
22
23		28	29	30	31	1	2	3
24									2
25									5
26
27
28		4	5	Notes
29
30
31
32
January

Cell Formulas
Range	Formula
B1	B1	="January "&CalendarYear
B2	B2	=WeekStart
C2:H2	C2	=UPPER(TEXT(C3,"dddd"))
B3:H3	B3	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+1
B8:H8	B8	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+8
B13:H13	B13	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+15
B18:H18	B18	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+22
B23:H23	B23	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+29
B28:C28	B28	=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
CalendarYear	=January!$K$3	B1, B28:C28, B23:H23, B18:H18, B13:H13, B8:H8, B3:H3
WeekStart	=January!$K$7	B2, B28:C28, B23:H23, B18:H18, B13:H13, B8:H8, B3:H3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B28:D31	Expression	=AND(DAY(B28)>=1,DAY(B28)<=15)	text	NO
B3:G6	Expression	=DAY(B3)>8	text	NO
B23:H26	Expression	=AND(DAY(B23)>=1,DAY(B23)<=15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:H2	Any value
B2	Any value
B3:B6	Any value
B7	Any value
D28:D32	Any value
E28:H32	Any value
A1	Any value
B1	Any value
C1	Any value

Any year calendar (single month per tab)1
	A	B	C	D	E	F	G	H	I
1
2		Teams
3
4		Team 1	Team 2	Team 3	Team 4	Team 5	Team 6
5		1	2	3	4	5	6
6		Brandun	Ramy	Israel	Kedrin	Venro
7		Tuzzo	Angel		Josh	new guy
8
9
10
11		Team Leads
12
13		1	2	3
14		Jeff	Justin	Carl
15
16
17
18
19
teams

i need a formula that will auto fill certain cells in Colum h base on the value of the helper cell i have in column i. the top number in each pair of helper cells refers to a team lead reference and the bottom number refers to a team reference. for the numbers in rows 4, 9, 14, 19, and 24 i need the adjacent cell in h to show the the corresponding team lead referenced in row 14 of the second table (jeff, Justin and carl). for the number in rows 5, 10, 14, 19, and 25 i need the adjacent cell and the one beneath it to show the names of the team members of the corresponding teams. is there a good way of doing this. any help would be appreciated.