Spreadsheet: 809027d1670847671-looking-up-two-cells-to-find-result-in-another-cell-match-and-index-example-rota-and-appt-number-planning
Sheet: Sheet1

 |  |  | WEEK 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Notes/Changes
 |  |  |  | Room | AMC - ROOM 1 | AMC - ROOM 2 | AMC - ROOM 3 | AMC - ROOM 4 | AMC - ROOM 5 | AMC - ROOM 6 | AMC - ROOM 7 | AMC - ROOM 8 (minor ops) | AMC - ROOM 9 (treatment rm) | AMC - GoTo RECEPTION | AMC - GoTo RECEPTION 2 | AMC - ROOM 10 HOT ROOM | E-consult Desk | HOME | HOME | CP - ROOM 1 | CP - ROOM 2 | CP - ROOM 3 | CP - ROOM 4 | CP - TR | CP - R | CP - ECG
 | Monday
WK 1 |  | 2022-12-05 00:00:00 | AM | Name |  | SL | EB |  |  |  |  |  |  | RP | AW |  |  |  |  |  |  | TM | ZS
 |  |  |  | Shift |  | [NAME] | S/D ACP |  |  |  |  |  |  | Go2GP | SIGNPOSTING & Results |  |  |  |  |  |  | ACP Green | RED BLOODS
 |  |  | PM | Name |  |  | EB |  | BR |  |  |  |  | RP | SL |  | AW |  |  |  |  | TM | ZS
 |  |  |  | Shift |  |  | S/D ACP |  | S/D GP |  |  |  |  | Go2GP | SIGNPOSTING & Results |  | HV ACP |  |  |  |  | S/D ACP | E Consults |  |  |  | Move BR back to AMC in the afternoon - more likely to be filled
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  | RP
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  | Extended Access ([NAME] only)
 | Tuesday |  | AM | Name |  | SL | J Wadey |  |  |  |  | RP |  | MH | TM |  |  | AW | JM |  |  | EB | YM
 |  |  |  | Shift |  | S/D ACP | [NAME] |  |  |  |  | Microsuction |  | Go2GP | SIGNPOSTING & Results |  |  | NH WR | PCN |  |  | REPORTS | RED BLOODS |  |  |  | MH to be Go2GP all day
 |  |  | PM | Name |  |  | TM | RP |  |  |  |  |  | MH | SL |  | J Wadey | AW | JM |  |  | EB | YM |  |  |  | [NAME] to move back to Coppice to do Imms
 |  |  |  | Shift |  |  | S/D ACP | S/D GP |  |  |  |  |  | Go2GP | SIGNPOSTING & Results |  | HV ACP | NH WR | [NAME] |  |  | Hyper | E Consults
 |  |  |  | Jobs |  |  | TM |  |  |  |  |  |  | YM
 |  |  |  | Jobs |  |  | Extended Access |  |  |  |  |  |  | Extended Access
 | Wednesday |  | AM | Name | J Wadey | ZS |  | TM |  |  | AW | JM or MH | MC | BR | EB |  |  |  |  |  |  |  | JM or MH
 |  |  |  | Shift | [NAME] | [NAME] |  | S/D ACP |  |  | [NAME] | Minor Ops Clinic | Minor Ops Clinic | Go2GP | SIGNPOSTING & Results |  |  |  |  |  |  |  | RED BLOODS |  |  |  | Alternate S/D GP with LTC for Zoe in the afternoon? So that JM can do some greens?
 |  |  | PM | Name | JM or ZS | ZS or MH | EB | J Wadey |  |  |  |  |  | BR | AW |  | TM |  |  |  |  |  | JM or MH |  |  |  | Or, Alternate JM and S/D GP with MH at Coppice?
 |  |  |  | Shift | S/D GP | [NAME] | ACP Green | S/D ACP |  |  |  |  |  | Go2GP | SIGNPOSTING & Results |  | HV ACP |  |  |  |  |  | E Consults |  |  |  | Who do to urines at Coppice? Can a nurse do instead?
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  | JM | AW |  | TM
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  | Extended Access | Extended Access |  | Extended Access
 | Thursday |  | AM | Name |  | SL |  |  | MH |  | AW |  |  | ZS | TM | J Wadey |  | JM |  | Social Prescribers |  |  | RP
 |  |  |  | Shift |  | [NAME] |  |  | Practice [NAME] |  | [NAME] |  |  | Go2GP | SIGNPOSTING & Results | S/D ACP |  | Practice [NAME] |  |  |  |  | RED BLOODS
 |  |  | PM | Name |  | SL |  |  | MH |  | AW |  |  | ZS | AW | J Wadey | TM |  |  | Social Prescribers |  |  | RP
 |  |  |  | Shift |  | HV ACP |  |  | S/D GP |  | S/D ACP |  |  | Go2GP | SIGNPOSTING & Results | ADHD | [NAME] |  |  |  |  |  | E Consults
 |  |  |  | Jobs |  |  | ?? |  | MH
 |  |  |  | Jobs |  |  | Extended Access |  | Extended Access
 | Friday |  | AM | Name |  | SL | J Wadey | YM |  |  |  |  |  | JM | TM |  |  |  |  |  |  |  | ZS
 |  |  |  | Shift |  | S/D ACP | [NAME] | GP Green |  |  |  |  |  | Go2GP | SIGNPOSTING & Results |  |  |  |  |  |  |  | RED BLOODS
 |  |  | PM | Name |  | SL |  | YM |  |  |  |  |  | JM | TM |  | J Wadey | MH | RP |  |  |  | ZS
 |  |  |  | Shift |  | S/D ACP |  | S/D GP |  |  |  |  |  | Go2GP | SIGNPOSTING & Results |  | HV ACP | [NAME] | [NAME] |  |  |  | E Consults
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  | Extended Access ([NAME] only) |  |  |  |  |  |  |  |  |  | Check Defib/Oxygen | [NAME]
 |  |  |  | Jobs |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Check Defib/Oxygen | [NAME]
 | Saturday |  | AM | Name |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACP |  |  | Partner
 |  |  |  | Shift |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Extended Access ([NAME] only) |  |  | Extended Access ([NAME] only)
 |  |  | PM | Name |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACP |  |  | Partner
 |  |  |  | Shift |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Extended Access ([NAME] only) |  |  | Extended Access ([NAME] only)
 |  |  |  | Jobs
 |  |  |  | Jobs
 | Appointment Type | Session Type Used | JM | MH | RP | YM | BR | ZS | AW | SL | EB | TM | J Wadey | JK | AD | RR | CM | MC | SOC | LL
 | Same Day Appt | S/D GP
 | Medical Reports/Baby Checks | RED BLOODS
 | Same Day Appt | S/D ACP |  |  |  |  |  |  |  | = Count of S/D ACP Sessions done by [NAME], the lookup the number of Same Day Appt he does on sheet 2 within the S/D ACP session type'. In this case it would be 9 appointments every time [NAME] does the Session Type. In the rota above he is doing S/D ACP 3 times (once on Tuesday morning and then twice on Friday) so, he is providing 3 x 9 (see sheet 2) "Same Day Appt" Slots. = 27 appointments of that type in the week |  |  |  |  |  |  |  | Hi both of you!
 | [NAME] | [NAME]
 | [NAME] | ACP Green |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sorry about the delay in responding and thank you both for looking at this.
 | Red Bloods | RED BLOODS
 | E-Consults | E Consults |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I appreciate, when you are "in" it, it can make perfect sense to you and then try and explain it to someone else and it all goes a bit pear shaped! :)
 | Dressings | DRESSINGS - AM
 | Dressings | DRESSINGS - PM
 | Bloods | Bloods Clinic |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | So, what I am after is:
 | B12 | Bloods Clinic |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1. For the week, to total up the available appointments for each APPOINTMENT TYPE (e.g. Same Day Appt, LTC Appt
 | ABPM | Bloods Clinic |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2. So, for example, each SESSION TYPE that is called "S/D ACP" would have a certain number of Same Day Appt slots being seen in that session.
 | DM1 | Bloods Clinic |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3. If these were the same for each person, this would be simple (ish) to do.
 | HRT | Nurse Session - AM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4. But, my problem is that one person (e.g. AW) would do, say, 10 appts within that SESSION TYPE whereas another person (e.g. [NAME]) would only do 9.
 | Ear Checks | Nurse Session - PM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5. So, I need to lookup, according to the rota, who is doing a particular SESSION TYPE, then look at Sheet 2 to find out how many of the APPOINTMENT TYPE slots they are doing - and summarise that for the whole week.
 | General [NAME] | Nurse Session - PM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6. It would use the items in B50 through to [NAME] as the appointment type that I am trying to total based on what is the rota above.
 | [NAME] | [NAME]
 | DM2 | Nurse DM Clinic - AM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Really not sure I have set it up right to be able to do it - to be honest.
 | DM2 | Nurse DM Clinic - PM
 | [NAME] | ADHD
 | [NAME] | GP Green
 | Smears | Smears
 | Imms | Imms
 | Mental Health - SMI | Mental Health
 | HCA | HCA Session
 | NHS Health Checks | NHS HEALTH CHECKS
 | Home Visits | HV ACP
 | Nursing Home Ward Rounds | NH WR
 | Asthma | Asthma Clinic