IF statement = the second occurrence, then paste that corresponding row's second column

[twickmanmd]

Can someone help me find a shorter formula. I would like the corresponding row's first column to appear in successive columns in a second sheet. Each F number (ie F1, F2, etc) refers to a person. the cells to the right of each person refer to a job that they will be doing (ie Team 1, Team 2, team transplant, etc). I would like the person to be placed in a cell corresponding to the team that they are on during those weeks. If two people on on that team, then I need the second person to appear in the column next to them.
I am using one sheet,

2021_2022_fellow_schedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	START DATE	4-Jul	1-Aug	29-Aug	26-Sep	24-Oct	21-Nov	19-Dec	16-Jan	13-Feb	13-Mar	10-Apr	8-May	5-Jun
2	WEEKS	1-4	5-8	9-12	13-16	17-20	21-24	25-28	29-32	33-36	37-40	41-44	45-48	49-52	TOTAL 1	TOTAL 2	TOTAL Transplant	TOTAL ESKD	TOTAL Elective	TOTAL Interventional	TOTAL Palliative	Total Months
3	F1	elective	1	transplant	2	elective	1	2	interventional	transplant	ESKD	elective	1	2	3	3	2	1	3	1	0	13
4	F2	1	transplant	2	elective	1	2	interventional	transplant	ESKD	elective	1	2	elective	3	3	2	1	3	1	0	13
5	F3	transplant	2	elective	1	2	interventional	transplant	ESKD	elective	1	2	elective	1	3	3	2	1	3	1	0	13
6	F4	2	elective	1	elective	elective	transplant	ESKD	elective	1	2	interventional	elective	palliative	2	2	1	1	5	1	1	13
7	F5	elective	1	2	elective	transplant	ESKD	elective	1	2	interventional	elective	palliative	elective	2	2	1	1	5	1	1	13
8	F6	1	2	elective	transplant	ESKD	elective	1	2	interventional	elective	palliative	elective	elective	2	2	1	1	5	1	1	13
9	F7											2	elective	1	1	1	0	0	1	0	0	3
10
11
MonthlyB

Cell Formulas
Range	Formula
O3:O9	O3	=COUNTIF(B3:N3,"1")
P3:P9	P3	=COUNTIF(B3:N3,"2")
Q3:Q9	Q3	=COUNTIF(B3:N3,"transplant")
R3:R9	R3	=COUNTIF(B3:N3,"ESKD")
S3:S9	S3	=COUNTIF(B3:N3,"elective")
T3:T9	T3	=COUNTIF(B3:N3,"interventional")
U3:U9	U3	=COUNTIF(B3:N3,"palliative")
V3:V9	V3	=SUM(O3:U3)
A3:A9	A3	=Overdraft!C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A9	Cell Value	contains ""	text	NO
B3:N9	Cell Value	contains "interventional"	text	NO
A3:A9	Cell Value	contains ""	text	NO
A3:A9	Cell Value	contains ""	text	NO
A3:A9	Cell Value	contains ""	text	NO
A3:A9	Cell Value	contains ""	text	NO
A3:A9	Cell Value	contains ""	text	NO
A3:A9	Cell Value	contains ""	text	NO
B3:N9	Cell Value	contains "palliative"	text	NO
B3:N9	Cell Value	contains "elective"	text	NO
B3:N9	Cell Value	contains "transplant"	text	NO
B3:N9	Cell Value	contains "2"	text	NO
B3:N9	Cell Value	contains "1"	text	NO
B3:N9	Cell Value	contains "ESKD"	text	NO

to fill out another,

2021_2022_fellow_schedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	ROTATION	DATE	WEEK	CALL	TEAM 1	TEAM 1	TEAM 2	TEAM 2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE	VACATION	VACATION	VACATION	VACATION	BR KPRC
3		Sunday, June 27, 2021	0	F6	F2	F6	F4	FALSE	FALSE	F3	FALSE	FALSE	F1	F5			FALSE
4	1	Sunday, July 4, 2021	1	F5	F2	F6	F4	FALSE	FALSE	F3	FALSE	FALSE	F1	F5			FALSE
5		Sunday, July 11, 2021	2	F4	F2	F6	F4	FALSE	FALSE	F3	FALSE	FALSE	F1	F5			FALSE
6		Sunday, July 18, 2021	3	F3	F2	F6	F4	FALSE	FALSE	F3	FALSE	FALSE	F1	F5			FALSE
7		Sunday, July 25, 2021	4	F2	F2	F6	F4	FALSE	FALSE	F3	FALSE	FALSE	F1	F5			FALSE
8	2	Sunday, August 1, 2021	5	F1	F1	F5	F3	F6	FALSE	F2	FALSE	FALSE	F4	FALSE			FALSE
9		Sunday, August 8, 2021	6	F6	F1	F5	F3	F6	FALSE	F2	FALSE	FALSE	F4	FALSE			FALSE
Weekly

Cell Formulas
Range	Formula
D3:D9	D3	=Call_Lecture!C2
E3	E3	=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
F3	F3	=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
G3	G3	=IF(MonthlyB!B3=2,MonthlyB!A3,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
H3	H3	=IF(MonthlyB!B3=2,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=2,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=2,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=2,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=2,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
I3	I3	=IF(MonthlyB!B3="ESKD",MonthlyB!A3,IF(MonthlyB!B4="ESKD",MonthlyB!A4,IF(MonthlyB!B5="ESKD",MonthlyB!A5,IF(MonthlyB!B6="ESKD",MonthlyB!A6,IF(MonthlyB!B7="ESKD",MonthlyB!A7,IF(MonthlyB!B8="ESKD",MonthlyB!A8,"FALSE"))))))
J3	J3	=IF(MonthlyB!B3="transplant",MonthlyB!A3,IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
K3	K3	=IF(MonthlyB!B3="transplant",IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="transplant",IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="transplant",IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="transplant",IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="transplant",IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
L3	L3	=IF(MonthlyB!B3="interventional",MonthlyB!A3,IF(MonthlyB!B4="interventional",MonthlyB!A4,IF(MonthlyB!B5="interventional",MonthlyB!A5,IF(MonthlyB!B6="interventional",MonthlyB!A6,IF(MonthlyB!B7="interventional",MonthlyB!A7,IF(MonthlyB!B8="interventional",MonthlyB!A8,"FALSE"))))))
M3	M3	=IF(MonthlyB!B3="elective",MonthlyB!A3,IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
N3	N3	=IF(MonthlyB!B3="elective",IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="elective",IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="elective",IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="elective",IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="elective",IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
E4	E4	=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
F4	F4	=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
G4	G4	=IF(MonthlyB!B3=2,MonthlyB!A3,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
H4	H4	=IF(MonthlyB!B3=2,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=2,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=2,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=2,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=2,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
I4	I4	=IF(MonthlyB!B3="ESKD",MonthlyB!A3,IF(MonthlyB!B4="ESKD",MonthlyB!A4,IF(MonthlyB!B5="ESKD",MonthlyB!A5,IF(MonthlyB!B6="ESKD",MonthlyB!A6,IF(MonthlyB!B7="ESKD",MonthlyB!A7,IF(MonthlyB!B8="ESKD",MonthlyB!A8,"FALSE"))))))
J4	J4	=IF(MonthlyB!B3="transplant",MonthlyB!A3,IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
K4	K4	=IF(MonthlyB!B3="transplant",IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="transplant",IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="transplant",IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="transplant",IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="transplant",IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
L4	L4	=IF(MonthlyB!B3="interventional",MonthlyB!A3,IF(MonthlyB!B4="interventional",MonthlyB!A4,IF(MonthlyB!B5="interventional",MonthlyB!A5,IF(MonthlyB!B6="interventional",MonthlyB!A6,IF(MonthlyB!B7="interventional",MonthlyB!A7,IF(MonthlyB!B8="interventional",MonthlyB!A8,"FALSE"))))))
M4	M4	=IF(MonthlyB!B3="elective",MonthlyB!A3,IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
N4	N4	=IF(MonthlyB!B3="elective",IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="elective",IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="elective",IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="elective",IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="elective",IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
E5	E5	=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
F5	F5	=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
G5	G5	=IF(MonthlyB!B3=2,MonthlyB!A3,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
H5	H5	=IF(MonthlyB!B3=2,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=2,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=2,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=2,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=2,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
I5	I5	=IF(MonthlyB!B3="ESKD",MonthlyB!A3,IF(MonthlyB!B4="ESKD",MonthlyB!A4,IF(MonthlyB!B5="ESKD",MonthlyB!A5,IF(MonthlyB!B6="ESKD",MonthlyB!A6,IF(MonthlyB!B7="ESKD",MonthlyB!A7,IF(MonthlyB!B8="ESKD",MonthlyB!A8,"FALSE"))))))
J5	J5	=IF(MonthlyB!B3="transplant",MonthlyB!A3,IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
K5	K5	=IF(MonthlyB!B3="transplant",IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="transplant",IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="transplant",IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="transplant",IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="transplant",IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
L5	L5	=IF(MonthlyB!B3="interventional",MonthlyB!A3,IF(MonthlyB!B4="interventional",MonthlyB!A4,IF(MonthlyB!B5="interventional",MonthlyB!A5,IF(MonthlyB!B6="interventional",MonthlyB!A6,IF(MonthlyB!B7="interventional",MonthlyB!A7,IF(MonthlyB!B8="interventional",MonthlyB!A8,"FALSE"))))))
M5	M5	=IF(MonthlyB!B3="elective",MonthlyB!A3,IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
N5	N5	=IF(MonthlyB!B3="elective",IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="elective",IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="elective",IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="elective",IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="elective",IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
E6	E6	=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
F6	F6	=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
G6	G6	=IF(MonthlyB!B3=2,MonthlyB!A3,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
H6	H6	=IF(MonthlyB!B3=2,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=2,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=2,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=2,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=2,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
I6	I6	=IF(MonthlyB!B3="ESKD",MonthlyB!A3,IF(MonthlyB!B4="ESKD",MonthlyB!A4,IF(MonthlyB!B5="ESKD",MonthlyB!A5,IF(MonthlyB!B6="ESKD",MonthlyB!A6,IF(MonthlyB!B7="ESKD",MonthlyB!A7,IF(MonthlyB!B8="ESKD",MonthlyB!A8,"FALSE"))))))
J6	J6	=IF(MonthlyB!B3="transplant",MonthlyB!A3,IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
K6	K6	=IF(MonthlyB!B3="transplant",IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="transplant",IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="transplant",IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="transplant",IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="transplant",IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
L6	L6	=IF(MonthlyB!B3="interventional",MonthlyB!A3,IF(MonthlyB!B4="interventional",MonthlyB!A4,IF(MonthlyB!B5="interventional",MonthlyB!A5,IF(MonthlyB!B6="interventional",MonthlyB!A6,IF(MonthlyB!B7="interventional",MonthlyB!A7,IF(MonthlyB!B8="interventional",MonthlyB!A8,"FALSE"))))))
M6	M6	=IF(MonthlyB!B3="elective",MonthlyB!A3,IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
N6	N6	=IF(MonthlyB!B3="elective",IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="elective",IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="elective",IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="elective",IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="elective",IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
E7	E7	=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
F7	F7	=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))
G7	G7	=IF(MonthlyB!B3=2,MonthlyB!A3,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
H7	H7	=IF(MonthlyB!B3=2,IF(MonthlyB!B4=2,MonthlyB!A4,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=2,IF(MonthlyB!B5=2,MonthlyB!A5,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=2,IF(MonthlyB!B6=2,MonthlyB!A6,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=2,IF(MonthlyB!B7=2,MonthlyB!A7,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=2,IF(MonthlyB!B8=2,MonthlyB!A8,"FALSE"))))))
I7	I7	=IF(MonthlyB!B3="ESKD",MonthlyB!A3,IF(MonthlyB!B4="ESKD",MonthlyB!A4,IF(MonthlyB!B5="ESKD",MonthlyB!A5,IF(MonthlyB!B6="ESKD",MonthlyB!A6,IF(MonthlyB!B7="ESKD",MonthlyB!A7,IF(MonthlyB!B8="ESKD",MonthlyB!A8,"FALSE"))))))
J7	J7	=IF(MonthlyB!B3="transplant",MonthlyB!A3,IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
K7	K7	=IF(MonthlyB!B3="transplant",IF(MonthlyB!B4="transplant",MonthlyB!A4,IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="transplant",IF(MonthlyB!B5="transplant",MonthlyB!A5,IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="transplant",IF(MonthlyB!B6="transplant",MonthlyB!A6,IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="transplant",IF(MonthlyB!B7="transplant",MonthlyB!A7,IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="transplant",IF(MonthlyB!B8="transplant",MonthlyB!A8,"FALSE"))))))
L7	L7	=IF(MonthlyB!B3="interventional",MonthlyB!A3,IF(MonthlyB!B4="interventional",MonthlyB!A4,IF(MonthlyB!B5="interventional",MonthlyB!A5,IF(MonthlyB!B6="interventional",MonthlyB!A6,IF(MonthlyB!B7="interventional",MonthlyB!A7,IF(MonthlyB!B8="interventional",MonthlyB!A8,"FALSE"))))))
M7	M7	=IF(MonthlyB!B3="elective",MonthlyB!A3,IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
N7	N7	=IF(MonthlyB!B3="elective",IF(MonthlyB!B4="elective",MonthlyB!A4,IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4="elective",IF(MonthlyB!B5="elective",MonthlyB!A5,IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5="elective",IF(MonthlyB!B6="elective",MonthlyB!A6,IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6="elective",IF(MonthlyB!B7="elective",MonthlyB!A7,IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!B7="elective",IF(MonthlyB!B8="elective",MonthlyB!A8,"FALSE"))))))
E8	E8	=IF(MonthlyB!C3=1,MonthlyB!A3,IF(MonthlyB!C4=1,MonthlyB!A4,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))))
F8	F8	=IF(MonthlyB!C3=1,IF(MonthlyB!C4=1,MonthlyB!A4,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4=1,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5=1,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6=1,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!C7=1,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))))
G8	G8	=IF(MonthlyB!C3=2,MonthlyB!A3,IF(MonthlyB!C4=2,MonthlyB!A4,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))))
H8	H8	=IF(MonthlyB!C3=2,IF(MonthlyB!C4=2,MonthlyB!A4,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4=2,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5=2,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6=2,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!C7=2,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))))
I8	I8	=IF(MonthlyB!C3="ESKD",MonthlyB!A3,IF(MonthlyB!C4="ESKD",MonthlyB!A4,IF(MonthlyB!C5="ESKD",MonthlyB!A5,IF(MonthlyB!C6="ESKD",MonthlyB!A6,IF(MonthlyB!C7="ESKD",MonthlyB!A7,IF(MonthlyB!C8="ESKD",MonthlyB!A8,"FALSE"))))))
J8	J8	=IF(MonthlyB!C3="transplant",MonthlyB!A3,IF(MonthlyB!C4="transplant",MonthlyB!A4,IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))))
K8	K8	=IF(MonthlyB!C3="transplant",IF(MonthlyB!C4="transplant",MonthlyB!A4,IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4="transplant",IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5="transplant",IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6="transplant",IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!C7="transplant",IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))))
L8	L8	=IF(MonthlyB!C3="interventional",MonthlyB!A3,IF(MonthlyB!C4="interventional",MonthlyB!A4,IF(MonthlyB!C5="interventional",MonthlyB!A5,IF(MonthlyB!C6="interventional",MonthlyB!A6,IF(MonthlyB!C7="interventional",MonthlyB!A7,IF(MonthlyB!C8="interventional",MonthlyB!A8,"FALSE"))))))
M8	M8	=IF(MonthlyB!C3="elective",MonthlyB!A3,IF(MonthlyB!C4="elective",MonthlyB!A4,IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))))
N8	N8	=IF(MonthlyB!C3="elective",IF(MonthlyB!C4="elective",MonthlyB!A4,IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4="elective",IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5="elective",IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6="elective",IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!C7="elective",IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))))
E9	E9	=IF(MonthlyB!C3=1,MonthlyB!A3,IF(MonthlyB!C4=1,MonthlyB!A4,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))))
F9	F9	=IF(MonthlyB!C3=1,IF(MonthlyB!C4=1,MonthlyB!A4,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4=1,IF(MonthlyB!C5=1,MonthlyB!A5,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5=1,IF(MonthlyB!C6=1,MonthlyB!A6,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6=1,IF(MonthlyB!C7=1,MonthlyB!A7,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!C7=1,IF(MonthlyB!C8=1,MonthlyB!A8,"FALSE"))))))
G9	G9	=IF(MonthlyB!C3=2,MonthlyB!A3,IF(MonthlyB!C4=2,MonthlyB!A4,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))))
H9	H9	=IF(MonthlyB!C3=2,IF(MonthlyB!C4=2,MonthlyB!A4,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4=2,IF(MonthlyB!C5=2,MonthlyB!A5,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5=2,IF(MonthlyB!C6=2,MonthlyB!A6,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6=2,IF(MonthlyB!C7=2,MonthlyB!A7,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE")),IF(MonthlyB!C7=2,IF(MonthlyB!C8=2,MonthlyB!A8,"FALSE"))))))
I9	I9	=IF(MonthlyB!C3="ESKD",MonthlyB!A3,IF(MonthlyB!C4="ESKD",MonthlyB!A4,IF(MonthlyB!C5="ESKD",MonthlyB!A5,IF(MonthlyB!C6="ESKD",MonthlyB!A6,IF(MonthlyB!C7="ESKD",MonthlyB!A7,IF(MonthlyB!C8="ESKD",MonthlyB!A8,"FALSE"))))))
J9	J9	=IF(MonthlyB!C3="transplant",MonthlyB!A3,IF(MonthlyB!C4="transplant",MonthlyB!A4,IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))))
K9	K9	=IF(MonthlyB!C3="transplant",IF(MonthlyB!C4="transplant",MonthlyB!A4,IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4="transplant",IF(MonthlyB!C5="transplant",MonthlyB!A5,IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5="transplant",IF(MonthlyB!C6="transplant",MonthlyB!A6,IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6="transplant",IF(MonthlyB!C7="transplant",MonthlyB!A7,IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE")),IF(MonthlyB!C7="transplant",IF(MonthlyB!C8="transplant",MonthlyB!A8,"FALSE"))))))
L9	L9	=IF(MonthlyB!C3="interventional",MonthlyB!A3,IF(MonthlyB!C4="interventional",MonthlyB!A4,IF(MonthlyB!C5="interventional",MonthlyB!A5,IF(MonthlyB!C6="interventional",MonthlyB!A6,IF(MonthlyB!C7="interventional",MonthlyB!A7,IF(MonthlyB!C8="interventional",MonthlyB!A8,"FALSE"))))))
M9	M9	=IF(MonthlyB!C3="elective",MonthlyB!A3,IF(MonthlyB!C4="elective",MonthlyB!A4,IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))))
N9	N9	=IF(MonthlyB!C3="elective",IF(MonthlyB!C4="elective",MonthlyB!A4,IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))),IF(MonthlyB!C4="elective",IF(MonthlyB!C5="elective",MonthlyB!A5,IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE")))),IF(MonthlyB!C5="elective",IF(MonthlyB!C6="elective",MonthlyB!A6,IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))),IF(MonthlyB!C6="elective",IF(MonthlyB!C7="elective",MonthlyB!A7,IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE")),IF(MonthlyB!C7="elective",IF(MonthlyB!C8="elective",MonthlyB!A8,"FALSE"))))))
Q3	Q3	=IF(MonthlyB!B3="palliative",MonthlyB!A3,IF(MonthlyB!B4="palliative",MonthlyB!A4,IF(MonthlyB!B5="palliative",MonthlyB!A5,IF(MonthlyB!B6="palliative",MonthlyB!A6,IF(MonthlyB!B7="palliative",MonthlyB!A7,IF(MonthlyB!B8="palliative",MonthlyB!A8,"FALSE"))))))
Q4	Q4	=IF(MonthlyB!B3="palliative",MonthlyB!A3,IF(MonthlyB!B4="palliative",MonthlyB!A4,IF(MonthlyB!B5="palliative",MonthlyB!A5,IF(MonthlyB!B6="palliative",MonthlyB!A6,IF(MonthlyB!B7="palliative",MonthlyB!A7,IF(MonthlyB!B8="palliative",MonthlyB!A8,"FALSE"))))))
Q5	Q5	=IF(MonthlyB!B3="palliative",MonthlyB!A3,IF(MonthlyB!B4="palliative",MonthlyB!A4,IF(MonthlyB!B5="palliative",MonthlyB!A5,IF(MonthlyB!B6="palliative",MonthlyB!A6,IF(MonthlyB!B7="palliative",MonthlyB!A7,IF(MonthlyB!B8="palliative",MonthlyB!A8,"FALSE"))))))
Q6	Q6	=IF(MonthlyB!B3="palliative",MonthlyB!A3,IF(MonthlyB!B4="palliative",MonthlyB!A4,IF(MonthlyB!B5="palliative",MonthlyB!A5,IF(MonthlyB!B6="palliative",MonthlyB!A6,IF(MonthlyB!B7="palliative",MonthlyB!A7,IF(MonthlyB!B8="palliative",MonthlyB!A8,"FALSE"))))))
Q7	Q7	=IF(MonthlyB!B3="palliative",MonthlyB!A3,IF(MonthlyB!B4="palliative",MonthlyB!A4,IF(MonthlyB!B5="palliative",MonthlyB!A5,IF(MonthlyB!B6="palliative",MonthlyB!A6,IF(MonthlyB!B7="palliative",MonthlyB!A7,IF(MonthlyB!B8="palliative",MonthlyB!A8,"FALSE"))))))
Q8	Q8	=IF(MonthlyB!C3="palliative",MonthlyB!A3,IF(MonthlyB!C4="palliative",MonthlyB!A4,IF(MonthlyB!C5="palliative",MonthlyB!A5,IF(MonthlyB!C6="palliative",MonthlyB!A6,IF(MonthlyB!C7="palliative",MonthlyB!A7,IF(MonthlyB!C8="palliative",MonthlyB!A8,"FALSE"))))))
Q9	Q9	=IF(MonthlyB!C3="palliative",MonthlyB!A3,IF(MonthlyB!C4="palliative",MonthlyB!A4,IF(MonthlyB!C5="palliative",MonthlyB!A5,IF(MonthlyB!C6="palliative",MonthlyB!A6,IF(MonthlyB!C7="palliative",MonthlyB!A7,IF(MonthlyB!C8="palliative",MonthlyB!A8,"FALSE"))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains "FALSE"	text	NO
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains ""	text	NO
D3:V55	Cell Value	contains ""	text	NO

Currently I am using the IF function (where MonthlyB is the monthly schedule as shown above) in the following two ways to capture the second person for the second column.

=IF(MonthlyB!B3=1,MonthlyB!A3,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))

=IF(MonthlyB!B3=1,IF(MonthlyB!B4=1,MonthlyB!A4,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))),IF(MonthlyB!B4=1,IF(MonthlyB!B5=1,MonthlyB!A5,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")))),IF(MonthlyB!B5=1,IF(MonthlyB!B6=1,MonthlyB!A6,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))),IF(MonthlyB!B6=1,IF(MonthlyB!B7=1,MonthlyB!A7,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE")),IF(MonthlyB!B7=1,IF(MonthlyB!B8=1,MonthlyB!A8,"FALSE"))))))

The problem is now I am realizing that to get this formula to work on the 4th occurrence would be very problematic. I was thinking of an array, but could wrap my head around how to get it to work.
Any suggestions?
Thanks in advance,
Terrance

 

[KRice]

I think you're right about using an array approach. Here is one implementation. You'll see that I inserted one helper row because I needed a consistent reference for the team names (e.g., one table refers to "team 1" while the other refers to "1"). You could either change the column headings to these shorthand helper row names or change the other table to say "team 1" rather than "1" (both of these can eliminate the need for a helper row), or simply hide the helper row if you don't want to see it. This single formula should pull in the relevant content from the other table. You'll want to recreate conditional formatting so that each team has its preferred color code. I wasn't sure what Week 0 is for...it appears to be a duplicate of whatever is found in Week 1?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					1	1	2	2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
2	ROTATION	DATE	WEEK	CALL	TEAM 1	TEAM 1	TEAM 2	TEAM 2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
3		Sunday, June 27, 2021	0	F6
4	1	Sunday, July 4, 2021	1	F5	F2	F6	F4			F3			F1	F5
5		Sunday, July 11, 2021	2	F4	F2	F6	F4			F3			F1	F5
6		Sunday, July 18, 2021	3	F3	F2	F6	F4			F3			F1	F5
7		Sunday, July 25, 2021	4	F2	F2	F6	F4			F3			F1	F5
8	2	Sunday, August 1, 2021	5	F1	F1	F5	F3	F6		F2			F4
9		Sunday, August 8, 2021	6	F6	F1	F5	F3	F6		F2			F4
10		Sunday, August 15, 2021	7	0	F1	F5	F3	F6		F2			F4
11		Sunday, August 22, 2021	8	0	F1	F5	F3	F6		F2			F4
12	3	Sunday, August 29, 2021	9	0	F4		F2	F5		F1			F3	F6
13		Sunday, September 5, 2021	10	0	F4		F2	F5		F1			F3	F6
14		Sunday, September 12, 2021	11	0	F4		F2	F5		F1			F3	F6
15		Sunday, September 19, 2021	12	0	F4		F2	F5		F1			F3	F6
16	4	Sunday, September 26, 2021	13	0	F3		F1			F6			F2	F4	F5
17		Sunday, October 3, 2021	14	0	F3		F1			F6			F2	F4	F5
18		Sunday, October 10, 2021	15	0	F3		F1			F6			F2	F4	F5
19		Sunday, October 17, 2021	16	0	F3		F1			F6			F2	F4	F5
20	5	Sunday, October 24, 2021	17	0	F2		F3		F6	F5			F1	F4
21		Sunday, October 31, 2021	18	0	F2		F3		F6	F5			F1	F4
22		Sunday, November 7, 2021	19	0	F2		F3		F6	F5			F1	F4
23		Sunday, November 14, 2021	20	0	F2		F3		F6	F5			F1	F4
24	6	Sunday, November 21, 2021	21	0	F1		F2		F5	F4		F3	F6
Weekly2

Cell Formulas
Range	Formula
D3:D24	D3	=Call_Lecture!C2
E4:Q24	E4	=IFERROR(INDEX(MonthlyB!$A$3:$A$9,AGGREGATE(15,6,(ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2))/(INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1),COUNTIF($E$1:E$1,E$1))),"")

 

[KRice]

I forgot to mention another issue I noticed. The dates in row 1 of the MonthlyB sheet, I'm assuming they are ever increasing as one moves to the right? Your example shows all of them as year 2021, which causes an issue with the array logic because they are not in ascending order. I assume the dates begin in 2021 and then roll over into year 2022 as one goes from week 28 to week 29. Here is a working example with the conditional formatting...for some reason the blank cells, which are filled with black, aren't quite described correctly in the summary block below the table.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					1	1	2	2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
2	ROTATION	DATE	WEEK	CALL	TEAM 1	TEAM 1	TEAM 2	TEAM 2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
3		Sunday, June 27, 2021	0	F6
4	1	Sunday, July 4, 2021	1	F5	F2	F6	F4			F3			F1	F5
5		Sunday, July 11, 2021	2	F4	F2	F6	F4			F3			F1	F5
6		Sunday, July 18, 2021	3	F3	F2	F6	F4			F3			F1	F5
7		Sunday, July 25, 2021	4	F2	F2	F6	F4			F3			F1	F5
8	2	Sunday, August 1, 2021	5	F1	F1	F5	F3	F6		F2			F4
9		Sunday, August 8, 2021	6	F6	F1	F5	F3	F6		F2			F4
10		Sunday, August 15, 2021	7	F5	F1	F5	F3	F6		F2			F4
11		Sunday, August 22, 2021	8	F4	F1	F5	F3	F6		F2			F4
12	3	Sunday, August 29, 2021	9	F3	F4		F2	F5		F1			F3	F6
13		Sunday, September 5, 2021	10	F2	F4		F2	F5		F1			F3	F6
14		Sunday, September 12, 2021	11	F1	F4		F2	F5		F1			F3	F6
15		Sunday, September 19, 2021	12	F6	F4		F2	F5		F1			F3	F6
16	4	Sunday, September 26, 2021	13	F5	F3		F1			F6			F2	F4	F5
17		Sunday, October 3, 2021	14	F4	F3		F1			F6			F2	F4	F5
18		Sunday, October 10, 2021	15	F3	F3		F1			F6			F2	F4	F5
19		Sunday, October 17, 2021	16	F2	F3		F1			F6			F2	F4	F5
20	5	Sunday, October 24, 2021	17	F1	F2		F3		F6	F5			F1	F4
21		Sunday, October 31, 2021	18	F6	F2		F3		F6	F5			F1	F4
22		Sunday, November 7, 2021	19	F5	F2		F3		F6	F5			F1	F4
23		Sunday, November 14, 2021	20	F4	F2		F3		F6	F5			F1	F4
24	6	Sunday, November 21, 2021	21	F3	F1		F2		F5	F4		F3	F6
Weekly2

Cell Formulas
Range	Formula
D3:D24	D3	=Call_Lecture!C2
E4:Q24	E4	=IFERROR(INDEX(MonthlyB!$A$3:$A$9,AGGREGATE(15,6,(ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2))/(INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1),COUNTIF($E$1:E$1,E$1))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:Q24	Cell	contains a blank value	text	NO
D3:Q24	Cell Value	contains "F6"	text	NO
D3:Q24	Cell Value	contains "F5"	text	NO
D3:Q24	Cell Value	contains "F4"	text	NO
D3:Q24	Cell Value	contains "F3"	text	NO
D3:Q24	Cell Value	contains "F2"	text	NO
D3:Q24	Cell Value	contains "F1"	text	NO

 

[twickmanmd]

I think you're right about using an array approach. Here is one implementation. You'll see that I inserted one helper row because I needed a consistent reference for the team names (e.g., one table refers to "team 1" while the other refers to "1"). You could either change the column headings to these shorthand helper row names or change the other table to say "team 1" rather than "1" (both of these can eliminate the need for a helper row), or simply hide the helper row if you don't want to see it. This single formula should pull in the relevant content from the other table. You'll want to recreate conditional formatting so that each team has its preferred color code. I wasn't sure what Week 0 is for...it appears to be a duplicate of whatever is found in Week 1?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					1	1	2	2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
2	ROTATION	DATE	WEEK	CALL	TEAM 1	TEAM 1	TEAM 2	TEAM 2	ESKD	TRANSPLANT	TRANSPLANT	INTERVENTIONAL	ELECTIVE	ELECTIVE	ELECTIVE	ELECTIVE	PALLIATIVE
3		Sunday, June 27, 2021	0	F6
4	1	Sunday, July 4, 2021	1	F5	F2	F6	F4			F3			F1	F5
5		Sunday, July 11, 2021	2	F4	F2	F6	F4			F3			F1	F5
6		Sunday, July 18, 2021	3	F3	F2	F6	F4			F3			F1	F5
7		Sunday, July 25, 2021	4	F2	F2	F6	F4			F3			F1	F5
8	2	Sunday, August 1, 2021	5	F1	F1	F5	F3	F6		F2			F4
9		Sunday, August 8, 2021	6	F6	F1	F5	F3	F6		F2			F4
10		Sunday, August 15, 2021	7	0	F1	F5	F3	F6		F2			F4
11		Sunday, August 22, 2021	8	0	F1	F5	F3	F6		F2			F4
12	3	Sunday, August 29, 2021	9	0	F4		F2	F5		F1			F3	F6
13		Sunday, September 5, 2021	10	0	F4		F2	F5		F1			F3	F6
14		Sunday, September 12, 2021	11	0	F4		F2	F5		F1			F3	F6
15		Sunday, September 19, 2021	12	0	F4		F2	F5		F1			F3	F6
16	4	Sunday, September 26, 2021	13	0	F3		F1			F6			F2	F4	F5
17		Sunday, October 3, 2021	14	0	F3		F1			F6			F2	F4	F5
18		Sunday, October 10, 2021	15	0	F3		F1			F6			F2	F4	F5
19		Sunday, October 17, 2021	16	0	F3		F1			F6			F2	F4	F5
20	5	Sunday, October 24, 2021	17	0	F2		F3		F6	F5			F1	F4
21		Sunday, October 31, 2021	18	0	F2		F3		F6	F5			F1	F4
22		Sunday, November 7, 2021	19	0	F2		F3		F6	F5			F1	F4
23		Sunday, November 14, 2021	20	0	F2		F3		F6	F5			F1	F4
24	6	Sunday, November 21, 2021	21	0	F1		F2		F5	F4		F3	F6
Weekly2

Cell Formulas
Range	Formula
D3:D24	D3	=Call_Lecture!C2
E4:Q24	E4	=IFERROR(INDEX(MonthlyB!$A$3:$A$9,AGGREGATE(15,6,(ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2))/(INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1),COUNTIF($E$1:E$1,E$1))),"")

Well sir...Looks like you have some real excel-fu. Unbelievable! And it works! and can be autofilled! I am not nearly as advanced and attempted to look up each function. Took all day today to come close to wrapping my head around it. Can I try to summarize to see if I understand what you are doing?
You are using the INDEX function to return the contents of MonthlyB$A$3:$A$9 to insert the Person (eg. "F1") and you are using the AGGREGATE function to determine which cell in that reference to use. The hardest part for me to grasp was the contents of the AGGREGATE function. You divided the ROW by the team (eg. "1" or "2" or "ESKD") that is in the column of the DATE or DATE that is lower if not matching?
If the team column equals the team, count the number of them in the row including that cell (eg. if this is the 4th "elective" column in the row, return the answer 4). Use this number to find the AGGREGATE?
I will have to do more tomorrow. Too complicated for my little brain.
Very much thank you!

 

[KRice]

Thanks for the feedback...and welcome to the Board! I'm glad it's working for you. A single formula should be easier to maintain and offer better adaptability should table dimensions change. A few quick responses first:

You are using the INDEX function to return the contents of MonthlyB$A$3:$A$9 to insert the Person (eg. "F1")

Correct

you are using the AGGREGATE function to determine which cell in that reference to use

Also correct

Based on your comment/question about AGGREGATE, the explanation below might help to clarify. We'll start with the entire formula:
=IFERROR(INDEX(MonthlyB!$A$3:$A$9,AGGREGATE(15,6,(ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2))/(INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1),COUNTIF($E$1:E$1,E$1))),"")

Now strip away the obvious: the entire formula is wrapped in an IFERROR function that returns a blank ("") if an error is generated in the main formula, which leaves us with a basic INDEX(range,row) function. In this function, the "range" of interest is MonthlyB!$A$3:$A$9, the list of people on sheet MonthlyB. It is conceivable that more than one person will serve on the same team during any given week, so the challenge is to determine the row indexes associated with the people (staff) who will populate a team on any given date. The "row" part of this formula is the more difficult to understand, and is ultimately provided by the AGGREGATE function.

As an example of why the IFERROR wrapping is convenient, consider the case where only one staff member is assigned to Team 2, and since the Weekly table has two columns labeled Team 2, the formula will attempt to fill the second Team 2 column with a person's names. Since there is no second person to fill that slot, the INDEX function will give an error. The IFERROR wrapping suppresses the error code and simply returns a blank.

Now we'll dig into the AGGREGATE function, which takes four parameters: AGGREGATE( 15, 6, [messy array], [some integer] )

1. The 3rd parameter is the array that will hold the relevant row indexes.
2. The 1st parameter, in this case a "15", says to return the smaller values in the array. The AGGREGATE function is handy for extracting large and small values from arrays...a parameter of "14" says to return larger values.
3. The 2nd parameter, in this case a "6", says to ignore any errors that might be generated as we form the array of interest.
4. The 4th parameter specifies which of the smaller or larger values to return...so, for example, a 15 (1st parameter) coupled with a 2 (4th parameter) means to return the 2nd smallest value in the array.

The main challenge is to form the messy array for the 3rd parameter: (ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2))/(INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1)
We are given a specific date on the Weekly sheet. This is in column B, and the date changes as we move down row by row. We'll take an example where we are on row 4, associated with July 4, 2021 (found in cell $B4). This specific date will be associated with only one date block in the MonthlyB data table, and we can find the index of that column using: MATCH($B4,MonthlyB!$B$1:$N$1,1)
Since MonthlyB!$B$1:$N$1 holds the "start dates" of every 4 week block, and the dates are in ascending order (it is critical to ensure that the years are correct), the last parameter in the MATCH function (1) will find the largest date that is less than or equal to the queried date (Weekly!$B4). There are 13 columns represented by the range MonthlyB!$B$1:$N$1, so this MATCH function returns a single integer between 1 and 13, inclusive, that indicates the column index in the MonthlyB data table---the relevant time block---corresponding to the Weekly!$B4 date. If the $B4 query date is less than the first date in the MonthlyB range, MATCH will generate an error.

Then we use MATCH in conjunction with the INDEX function like this: INDEX(MonthlyB!$B$3:$N$9, , MATCH($B4,MonthlyB!$B$1:$N$1,1) )
A typical implementation of the INDEX function is INDEX(array, row, column). In this case, the 2nd parameter is left blank, and the default is to consider all rows in the specified array.
By using the result of the MATCH function as the 3rd parameter in this INDEX function, INDEX will return a vertical slice, one-column wide, of the MonthlyB data table. As an example, for the first block of time (weeks 1-4), MATCH will return a 1, and this INDEX function will return the array: {"elective";1;"transplant";2;"elective";1;0}
...which you can read directly down the first column of the MonthlyB table, the vertical slice corresponding to weeks 1-4.

But notice that the return from this INDEX function is used immediately to perform a logic test:
INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1
The first row of the Weekly sheet contains the team lookup terms that exactly match those used in the MonthlyB data table. In this example, Weekly!E$1 is "1" (meaning Team 1). As we move across the Weekly table, we encounter either duplicate team names (if there is some potential for multiple staff to be assigned to the same team) or other team names. So this logic test identifies which array elements in the vertical slice---that corresponds to the time block of interest---also correspond to the team name represented by the column in the Weekly table. The result is a variant of the vertical table slice that looks like this: {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
This means that the 2nd and 6th array elements (row indexes) are associated with Team 1 during the Week 1-4 time block. We'll use this array of TRUE's and FALSE's below as the denominator to form the "messy array" mentioned earlier.

We need some strategy for turning this array into values of 2 and 6. There are 7 elements in this array and they correspond to positions that are aligned with the list of staff members in MonthlyB!A3:A9. So in the simplest sense, we would like to generate a row-indexing array that looks like {1;2;3;4;5;6;7}. In fact, we could simply use that hard-wired array directly in the formula, but I prefer to form the array by referring to parts of the relevant table. The entire numerator is (ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2)). The ROW function returns the actual row number of the range specified (e.g., ROW(B3)=3), or ROW(MonthlyB!$B$3:$B$9) produces an array {3;4;5;6;7;8;9}. For convenience, we want this array to begin with "1", so we subtract the row number just above the range (ROW(MonthlyB!$B$2), and the end result is the desired array: {1;2;3;4;5;6;7}

Now the division takes place to form the "messy array" that is the 3rd parameter in the AGGREGATE function. We take the numerator just described divided by the TRUE/FALSE denominator array:
(ROW(MonthlyB!$B$3:$B$9)-ROW(MonthlyB!$B$2)) / (INDEX(MonthlyB!$B$3:$N$9,,MATCH($B4,MonthlyB!$B$1:$N$1,1))=E$1)
...which is equivalent to:
{1;2;3;4;5;6;7} / {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
The interesting thing is that a mathematical operation like division coerces TRUE's to 1's and FALSE's to 0's. The array division is a direct element-by-element operation, so the result is:
{1/0; 2/1; 3/0; 4/0; 5/0; 6/1; 7/0} or...
{#DIV/0!; 2; #DIV/0!; #DIV/0!; #DIV/0!; 6; #DIV/0!}

Note the division by 0 errors. Since the AGGREGATE function's 2nd parameter is a "6", the function ignores them! And we are left with an array containing a 2 and a 6...these are the same 2 and 6 that we noticed several paragraphs above when we reviewed the array of TRUE's and FALSE's, but now they are in a usable form for specifying row indexes.

To determine which of these row indexes to deliver first (controlled by the 4th parameter in the AGGREGATE function), we use: COUNTIF($E$1:E$1,E$1)
As you mentioned, this counts how many times the name of the team is found as we move across the Weekly table helper row, always beginning at $E$1 and ending on the current column. Pay attention to how the range is described, and whether columns or rows are locked. Here, COUNTIF is locked to row 1, and considers the contents as we move across the columns. If at some point you determine that three Team 1 members are needed, a new column would be inserted into the Weekly table, it would be labeled "1" or "Team 1", and the formula would find up to three people through that vertical slice of the MonthlyB table if they have been assigned "1" for that time block.

Finally, we return to where we began: INDEX(range,row)
The logic-and-math-modified array holds the row indexes for the people who satisfy both the time block and team conditions. Each of these row indexes is delivered one at a time by the AGGREGATE function as the formula is copied across the row in the Weekly table.

I notice that you have Excel 365 (I don't), so I think some additional improvement might be possible. Specifically, the FILTER function offers some advantages and would likely result in a somewhat shorter formula, supplanting the AGGREGATE function.

Let me know if any more detail is needed.