I am trying to use the index and match to find what shift was working on which day.
B18 to AC21 has the values i am looking for.
I can get the index and match to work over one row. (B3-to AC 3 ) i am trying to match a date over the whole range of dates (B3:AC16)
The formula in AF 12 works to select the row, but choosing the column for the date is challenging.
What i would like my formula to do is find the column that has the date i am looking for then indicate what a selected shift would be working.
so for example shift 2 was Do on 13 Dec. i would like the formula to show what they were working on 1 october 2021
B18 to AC21 has the values i am looking for.
I can get the index and match to work over one row. (B3-to AC 3 ) i am trying to match a date over the whole range of dates (B3:AC16)
The formula in AF 12 works to select the row, but choosing the column for the date is challenging.
What i would like my formula to do is find the column that has the date i am looking for then indicate what a selected shift would be working.
so for example shift 2 was Do on 13 Dec. i would like the formula to show what they were working on 1 october 2021
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A3 | A3 | =IF(SUMPRODUCT(1*(MONTH(B3:AC3)=1))>=1,YEAR(AC3),"") |
| C3:AC16 | C3 | =B3+1 |
| A4:A16 | A4 | =IF(A3="",(IF(SUMPRODUCT(1*(MONTH(B4:AC4)=1))>=1,YEAR(AC4),"")),"") |
| B4:B16 | B4 | =AC3+1 |
| AF12 | AF12 | =INDEX(shifts,MATCH(AF10,Shiftsname,0),MATCH(AF11,B16:AC16,0)) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| dcolumns | ='Shift Roster'!$B$3:$AC$3 | C3, A3 |
| 'Shift Roster'!SHIFT1 | ='Shift Roster'!$B$18:$AC$18 | AF12 |
| 'Shift Roster'!SHIFT2 | ='Shift Roster'!$B$19:$AC$19 | AF12 |
| SHIFT2 | ='Shift Roster'!$B$7:$AC$7 | C7, A7 |
| 'Shift Roster'!SHIFT3 | ='Shift Roster'!$B$20:$AC$20 | AF12 |
| 'Shift Roster'!SHIFT4 | ='Shift Roster'!$B$21:$AC$21 | AF12 |
| shifts | ='Shift Roster'!$B$18:$AC$21 | AF12 |
| Shiftsname | ='Shift Roster'!$A$18:$A$21 | AF12 |
| wdates | ='Shift Roster'!$B$3:$AC$16 | C3, A3 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B3:AC16,AE15 | Expression | =OR(B3=$A$25:$C$29,B3=$H$25:$J$29,B3=$O$25:$Q$29,B3=$V$25:$X$29) | text | NO |
| A21:AC21 | Expression | =($A$22=4) | text | NO |
| A20:AC20 | Expression | =($A$22=3) | text | NO |
| A19:AC19 | Expression | =($A$22)=2 | text | NO |
| A18:AC18 | Expression | =($A$22=1) | text | NO |
| B3:AC16,AE15 | Expression | ="day(b9)<>1" | text | NO |
| B3:AC16,AE15 | Expression | =DAY(B3)=1 | text | NO |
| B2:AC21,AE15 | Expression | =(B$22)="N" | text | NO |
| B2:AC21,AE15 | Expression | =(B$22)="M" | text | NO |
| B2:AC21,AE15 | Expression | =(B$22)="DO" | text | NO |
| B2:AC21,AE15 | Expression | =(B$22)="NDO" | text | NO |
