Hello,
Given the table (Table3) representing the 1:1 slots between 2 participants with the following data:
I would like to dynamically query (via Cell S3) the Timeslots and respective Groups for the defined participant.
Thanks to Fluff, the timeslot query is already working (see this thread).
Could anyone please point me in the right direction on how to query the groups --> Dynamically calculate Column "U"?
Thanks in advance.
Given the table (Table3) representing the 1:1 slots between 2 participants with the following data:
Book1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
J | K | L | M | N | O | P | Q | R | S | T | U | |||
2 | TIMESLOT | Group 1 | Group 2 | Group 3 | Group 4 | Group 5 | Group 6 | Participant | Slots to participate | Group | ||||
3 | 09h00-09h15 | X8_1A - X8_B | X8_1E - X8_2B | X8_1B - X8_5 | X8_1C - X8_3 | X8_1D - X8_A | X8_2A - X8_6 | X8_1A | 09h00-09h15 | Group 1 | ||||
4 | 09h15-09h30 | X8_1A - X8_4 | X8_1C - X8_2A | X8_6 - X8_3 | X8_2C - X8_A | X8_2B - X8_B | X8_1D - X8_5 | 09h15-09h30 | Group 1 | |||||
5 | 09h30-09h45 | X8_2B - X8_6 | X8_1D - X8_3 | X8_2A - X8_5 | X8_1F - X8_2C | X8_A - X8_B | X8_1E - X8_4 | 10h30-10h45 | Group 1 | |||||
6 | 09h45-10h00 | X8_1D - X8_6 | X8_A - X8_4 | X8_1C - X8_B | X8_2A - X8_3 | X8_1F - X8_5 | X8_1B - X8_2B | 10h45-11h00 | Group 5 | |||||
7 | 10h00-10h15 | X8_1C - X8_6 | X8_1D - X8_2B | X8_1B - X8_A | X8_2C - X8_4 | X8_1F - X8_3 | X8_5 - X8_B | 11h00-11h15 | Group 3 | |||||
8 | 10h15-10h30 | X8_6 - X8_4 | X8_2B - X8_A | X8_2C - X8_B | X8_1B - X8_3 | X8_1D - X8_2A | X8_1C - X8_5 | 11h15-11h30 | Group 6 | |||||
9 | 10h30-10h45 | X8_1A - X8_6 | X8_2B - X8_4 | X8_2A - X8_A | X8_1F - X8_B | X8_1E - X8_3 | X8_1C - X8_2C | 11h30-11h45 | Group 6 | |||||
10 | 10h45-11h00 | X8_1F - X8_6 | X8_1D - X8_4 | X8_1C - X8_2B | X8_1E - X8_A | X8_1A - X8_3 | X8_2C - X8_5 | 12h00-12h15 | Group 5 | |||||
11 | 11h00-11h15 | X8_6 - X8_B | X8_5 - X8_4 | X8_1A - X8_A | X8_2B - X8_3 | X8_1B - X8_2C | X8_1E - X8_2A | 12h15-12h30 | Group 4 | |||||
12 | 11h15-11h30 | X8_6 - X8_A | X8_3 - X8_B | X8_1C - X8_4 | X8_1E - X8_5 | X8_1F - X8_2B | X8_1A - X8_2A | |||||||
13 | 11h30-11h45 | X8_1E - X8_6 | X8_5 - X8_3 | X8_1F - X8_A | X8_1B - X8_B | X8_2A - X8_4 | X8_1A - X8_2C | |||||||
14 | 11h45-12h00 | X8_4 - X8_3 | X8_1E - X8_B | X8_2B - X8_5 | X8_2C - X8_6 | X8_1C - X8_A | X8_1B - X8_2A | |||||||
15 | 12h00-12h15 | X8_2C - X8_3 | X8_1B - X8_4 | X8_1D - X8_B | X8_6 - X8_5 | X8_1A - X8_2B | X8_1F - X8_2A | |||||||
16 | 12h15-12h30 | X8_1F - X8_4 | X8_1D - X8_2C | X8_A - X8_3 | X8_1A - X8_5 | X8_2A - X8_B | X8_1B - X8_6 | |||||||
17 | 12h30-12h45 | X8_1E - X8_2C | X8_4 - X8_B | X8_5 - X8_A | ||||||||||
INPUT Overall |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T3:T11 | T3 | =FILTER(Table3[TIMESLOT],MMULT(--(ISNUMBER(SEARCH($S$3,Table3[[Group 1]:[Group 6]]))),SEQUENCE(COLUMNS(Table3[@[Group 1]:[Group 6]]),,,0))>0) |
Dynamic array formulas. |
I would like to dynamically query (via Cell S3) the Timeslots and respective Groups for the defined participant.
Thanks to Fluff, the timeslot query is already working (see this thread).
Could anyone please point me in the right direction on how to query the groups --> Dynamically calculate Column "U"?
Thanks in advance.