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

twickmanmd

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABCDEFGHIJKLMNOPQRSTUV
1START DATE4-Jul1-Aug29-Aug26-Sep24-Oct21-Nov19-Dec16-Jan13-Feb13-Mar10-Apr8-May5-Jun
2WEEKS1-45-89-1213-1617-2021-2425-2829-3233-3637-4041-4445-4849-52TOTAL 1TOTAL 2TOTAL TransplantTOTAL ESKDTOTAL ElectiveTOTAL InterventionalTOTAL PalliativeTotal Months
3F1elective1transplant2elective12interventionaltransplantESKDelective12332131013
4F21transplant2elective12interventionaltransplantESKDelective12elective332131013
5F3transplant2elective12interventionaltransplantESKDelective12elective1332131013
6F42elective1electiveelectivetransplantESKDelective12interventionalelectivepalliative221151113
7F5elective12electivetransplantESKDelective12interventionalelectivepalliativeelective221151113
8F612electivetransplantESKDelective12interventionalelectivepalliativeelectiveelective221151113
9F72elective111001003
10
11
MonthlyB
Cell Formulas
RangeFormula
O3:O9O3=COUNTIF(B3:N3,"1")
P3:P9P3=COUNTIF(B3:N3,"2")
Q3:Q9Q3=COUNTIF(B3:N3,"transplant")
R3:R9R3=COUNTIF(B3:N3,"ESKD")
S3:S9S3=COUNTIF(B3:N3,"elective")
T3:T9T3=COUNTIF(B3:N3,"interventional")
U3:U9U3=COUNTIF(B3:N3,"palliative")
V3:V9V3=SUM(O3:U3)
A3:A9A3=Overdraft!C4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A9Cell Valuecontains ""textNO
B3:N9Cell Valuecontains "interventional"textNO
A3:A9Cell Valuecontains ""textNO
A3:A9Cell Valuecontains ""textNO
A3:A9Cell Valuecontains ""textNO
A3:A9Cell Valuecontains ""textNO
A3:A9Cell Valuecontains ""textNO
A3:A9Cell Valuecontains ""textNO
B3:N9Cell Valuecontains "palliative"textNO
B3:N9Cell Valuecontains "elective"textNO
B3:N9Cell Valuecontains "transplant"textNO
B3:N9Cell Valuecontains "2"textNO
B3:N9Cell Valuecontains "1"textNO
B3:N9Cell Valuecontains "ESKD"textNO


to fill out another,

2021_2022_fellow_schedule.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2ROTATIONDATEWEEKCALLTEAM 1TEAM 1TEAM 2TEAM 2ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVEVACATIONVACATIONVACATIONVACATIONBR KPRC
3Sunday, June 27, 20210F6F2F6F4FALSEFALSEF3FALSEFALSEF1F5FALSE
41Sunday, July 4, 20211F5F2F6F4FALSEFALSEF3FALSEFALSEF1F5FALSE
5Sunday, July 11, 20212F4F2F6F4FALSEFALSEF3FALSEFALSEF1F5FALSE
6Sunday, July 18, 20213F3F2F6F4FALSEFALSEF3FALSEFALSEF1F5FALSE
7Sunday, July 25, 20214F2F2F6F4FALSEFALSEF3FALSEFALSEF1F5FALSE
82Sunday, August 1, 20215F1F1F5F3F6FALSEF2FALSEFALSEF4FALSEFALSE
9Sunday, August 8, 20216F6F1F5F3F6FALSEF2FALSEFALSEF4FALSEFALSE
Weekly
Cell Formulas
RangeFormula
D3:D9D3=Call_Lecture!C2
E3E3=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"))))))
F3F3=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"))))))
G3G3=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"))))))
H3H3=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"))))))
I3I3=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"))))))
J3J3=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"))))))
K3K3=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"))))))
L3L3=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"))))))
M3M3=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"))))))
N3N3=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"))))))
E4E4=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"))))))
F4F4=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"))))))
G4G4=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"))))))
H4H4=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"))))))
I4I4=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"))))))
J4J4=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"))))))
K4K4=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"))))))
L4L4=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"))))))
M4M4=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"))))))
N4N4=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"))))))
E5E5=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"))))))
F5F5=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"))))))
G5G5=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"))))))
H5H5=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"))))))
I5I5=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"))))))
J5J5=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"))))))
K5K5=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"))))))
L5L5=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"))))))
M5M5=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"))))))
N5N5=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"))))))
E6E6=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"))))))
F6F6=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"))))))
G6G6=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"))))))
H6H6=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"))))))
I6I6=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"))))))
J6J6=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"))))))
K6K6=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"))))))
L6L6=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"))))))
M6M6=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"))))))
N6N6=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"))))))
E7E7=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"))))))
F7F7=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"))))))
G7G7=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"))))))
H7H7=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"))))))
I7I7=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"))))))
J7J7=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"))))))
K7K7=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"))))))
L7L7=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"))))))
M7M7=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"))))))
N7N7=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"))))))
E8E8=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"))))))
F8F8=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"))))))
G8G8=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"))))))
H8H8=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"))))))
I8I8=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"))))))
J8J8=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"))))))
K8K8=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"))))))
L8L8=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"))))))
M8M8=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"))))))
N8N8=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"))))))
E9E9=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"))))))
F9F9=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"))))))
G9G9=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"))))))
H9H9=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"))))))
I9I9=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"))))))
J9J9=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"))))))
K9K9=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"))))))
L9L9=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"))))))
M9M9=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"))))))
N9N9=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"))))))
Q3Q3=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"))))))
Q4Q4=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"))))))
Q5Q5=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"))))))
Q6Q6=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"))))))
Q7Q7=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"))))))
Q8Q8=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"))))))
Q9Q9=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
CellConditionCell FormatStop If True
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains "FALSE"textNO
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains ""textNO
D3:V55Cell Valuecontains ""textNO


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.

Excel Formula:
=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"))))))
Excel Formula:
=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
 

Attachments

  • image1.png
    image1.png
    57.2 KB · Views: 1
  • image2.png
    image2.png
    30 KB · Views: 1

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
11122ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
2ROTATIONDATEWEEKCALLTEAM 1TEAM 1TEAM 2TEAM 2ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
3Sunday, June 27, 20210F6
41Sunday, July 4, 20211F5F2F6F4  F3  F1F5   
5Sunday, July 11, 20212F4F2F6F4  F3  F1F5   
6Sunday, July 18, 20213F3F2F6F4  F3  F1F5   
7Sunday, July 25, 20214F2F2F6F4  F3  F1F5   
82Sunday, August 1, 20215F1F1F5F3F6 F2  F4    
9Sunday, August 8, 20216F6F1F5F3F6 F2  F4    
10Sunday, August 15, 202170F1F5F3F6 F2  F4    
11Sunday, August 22, 202180F1F5F3F6 F2  F4    
123Sunday, August 29, 202190F4 F2F5 F1  F3F6   
13Sunday, September 5, 2021100F4 F2F5 F1  F3F6   
14Sunday, September 12, 2021110F4 F2F5 F1  F3F6   
15Sunday, September 19, 2021120F4 F2F5 F1  F3F6   
164Sunday, September 26, 2021130F3 F1  F6  F2F4F5  
17Sunday, October 3, 2021140F3 F1  F6  F2F4F5  
18Sunday, October 10, 2021150F3 F1  F6  F2F4F5  
19Sunday, October 17, 2021160F3 F1  F6  F2F4F5  
205Sunday, October 24, 2021170F2 F3 F6F5  F1F4   
21Sunday, October 31, 2021180F2 F3 F6F5  F1F4   
22Sunday, November 7, 2021190F2 F3 F6F5  F1F4   
23Sunday, November 14, 2021200F2 F3 F6F5  F1F4   
246Sunday, November 21, 2021210F1 F2 F5F4 F3F6    
Weekly2
Cell Formulas
RangeFormula
D3:D24D3=Call_Lecture!C2
E4:Q24E4=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))),"")
 
Last edited:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
11122ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
2ROTATIONDATEWEEKCALLTEAM 1TEAM 1TEAM 2TEAM 2ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
3Sunday, June 27, 20210F6
41Sunday, July 4, 20211F5F2F6F4  F3  F1F5   
5Sunday, July 11, 20212F4F2F6F4  F3  F1F5   
6Sunday, July 18, 20213F3F2F6F4  F3  F1F5   
7Sunday, July 25, 20214F2F2F6F4  F3  F1F5   
82Sunday, August 1, 20215F1F1F5F3F6 F2  F4    
9Sunday, August 8, 20216F6F1F5F3F6 F2  F4    
10Sunday, August 15, 20217F5F1F5F3F6 F2  F4    
11Sunday, August 22, 20218F4F1F5F3F6 F2  F4    
123Sunday, August 29, 20219F3F4 F2F5 F1  F3F6   
13Sunday, September 5, 202110F2F4 F2F5 F1  F3F6   
14Sunday, September 12, 202111F1F4 F2F5 F1  F3F6   
15Sunday, September 19, 202112F6F4 F2F5 F1  F3F6   
164Sunday, September 26, 202113F5F3 F1  F6  F2F4F5  
17Sunday, October 3, 202114F4F3 F1  F6  F2F4F5  
18Sunday, October 10, 202115F3F3 F1  F6  F2F4F5  
19Sunday, October 17, 202116F2F3 F1  F6  F2F4F5  
205Sunday, October 24, 202117F1F2 F3 F6F5  F1F4   
21Sunday, October 31, 202118F6F2 F3 F6F5  F1F4   
22Sunday, November 7, 202119F5F2 F3 F6F5  F1F4   
23Sunday, November 14, 202120F4F2 F3 F6F5  F1F4   
246Sunday, November 21, 202121F3F1 F2 F5F4 F3F6    
Weekly2
Cell Formulas
RangeFormula
D3:D24D3=Call_Lecture!C2
E4:Q24E4=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
CellConditionCell FormatStop If True
D3:Q24Cellcontains a blank value textNO
D3:Q24Cell Valuecontains "F6"textNO
D3:Q24Cell Valuecontains "F5"textNO
D3:Q24Cell Valuecontains "F4"textNO
D3:Q24Cell Valuecontains "F3"textNO
D3:Q24Cell Valuecontains "F2"textNO
D3:Q24Cell Valuecontains "F1"textNO
 

twickmanmd

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABCDEFGHIJKLMNOPQ
11122ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
2ROTATIONDATEWEEKCALLTEAM 1TEAM 1TEAM 2TEAM 2ESKDTRANSPLANTTRANSPLANTINTERVENTIONALELECTIVEELECTIVEELECTIVEELECTIVEPALLIATIVE
3Sunday, June 27, 20210F6
41Sunday, July 4, 20211F5F2F6F4  F3  F1F5   
5Sunday, July 11, 20212F4F2F6F4  F3  F1F5   
6Sunday, July 18, 20213F3F2F6F4  F3  F1F5   
7Sunday, July 25, 20214F2F2F6F4  F3  F1F5   
82Sunday, August 1, 20215F1F1F5F3F6 F2  F4    
9Sunday, August 8, 20216F6F1F5F3F6 F2  F4    
10Sunday, August 15, 202170F1F5F3F6 F2  F4    
11Sunday, August 22, 202180F1F5F3F6 F2  F4    
123Sunday, August 29, 202190F4 F2F5 F1  F3F6   
13Sunday, September 5, 2021100F4 F2F5 F1  F3F6   
14Sunday, September 12, 2021110F4 F2F5 F1  F3F6   
15Sunday, September 19, 2021120F4 F2F5 F1  F3F6   
164Sunday, September 26, 2021130F3 F1  F6  F2F4F5  
17Sunday, October 3, 2021140F3 F1  F6  F2F4F5  
18Sunday, October 10, 2021150F3 F1  F6  F2F4F5  
19Sunday, October 17, 2021160F3 F1  F6  F2F4F5  
205Sunday, October 24, 2021170F2 F3 F6F5  F1F4   
21Sunday, October 31, 2021180F2 F3 F6F5  F1F4   
22Sunday, November 7, 2021190F2 F3 F6F5  F1F4   
23Sunday, November 14, 2021200F2 F3 F6F5  F1F4   
246Sunday, November 21, 2021210F1 F2 F5F4 F3F6    
Weekly2
Cell Formulas
RangeFormula
D3:D24D3=Call_Lecture!C2
E4:Q24E4=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

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top