G'day all,
I am in need of one final formula for a project I have been working on.
The range of cells in question that need formatting are G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112.
An example of the criteria would be
IF K96 contains X0530 and if there is a value containing 0530 in K5:K9, then K96 is highlighted.
I would want the same to apply if the value in K96 was X1330, it searched for values containing 1330 and also X2130 search for values containing 2130.
Hopefully that's easy to follow.
Appreciate your help.
Cheers,
Hayden
I am in need of one final formula for a project I have been working on.
The range of cells in question that need formatting are G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112.
An example of the criteria would be
IF K96 contains X0530 and if there is a value containing 0530 in K5:K9, then K96 is highlighted.
I would want the same to apply if the value in K96 was X1330, it searched for values containing 1330 and also X2130 search for values containing 2130.
Hopefully that's easy to follow.
Appreciate your help.
Cheers,
Hayden
DIGITAL ROSTER - VERSION 3.0.xlsm | |||||
---|---|---|---|---|---|
K | L | M | |||
2 | THU | FRI | SAT | ||
3 | 13-Mar | 14-Mar | 15-Mar | ||
4 | |||||
5 | 0530PL/S | 0530PB | 2130PB | ||
6 | 1330BC | 1330PB | |||
7 | |||||
8 | |||||
9 | |||||
10 | |||||
11 | |||||
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | |||||
17 | |||||
18 | |||||
19 | 0530TL | OFF | OFF | ||
20 | |||||
21 | |||||
22 | 1330TL | 1330TL | 1330TL | ||
23 | |||||
24 | |||||
25 | 2130TL | 2130TL | 2130TL | ||
26 | |||||
27 | |||||
28 | OFF | OFF | OFF | ||
29 | |||||
30 | |||||
31 | 0700AD | 0530TL | 0530TL | ||
32 | |||||
33 | |||||
34 | |||||
35 | 1330PL/S | EDO | OFF | ||
36 | |||||
37 | |||||
38 | 0530NRN | 0530BC | 0530PB | ||
39 | |||||
40 | |||||
41 | OFF | 2130PB | 2130NCC | ||
42 | |||||
43 | |||||
44 | 1330NRN | 1330BC | 1330PB | ||
45 | |||||
46 | |||||
47 | 2130PB | 2130NCC | 2130PL/S | ||
48 | |||||
49 | |||||
50 | OFF | 0530NRN | 0530BC | ||
51 | |||||
52 | |||||
53 | EDO | OFF | OFF | ||
54 | |||||
55 | |||||
56 | 2130NCC | 2130PL/S | 2130BC | ||
57 | |||||
58 | |||||
59 | 0530PB | 0530CFD | OFF | ||
60 | |||||
61 | |||||
62 | OFF | 1330NRN | 1330BC | ||
63 | |||||
64 | |||||
65 | EDO | OFF | OFF | ||
66 | |||||
67 | |||||
68 | 0530CFD | 0530PL/S | 0530NCC | ||
69 | |||||
70 | |||||
71 | EDO | OFF | OFF | ||
72 | |||||
73 | |||||
74 | 1330CFD | 1330PL/S | OFF | ||
75 | |||||
76 | |||||
77 | 1330PB | 1330CFD | 1330PL/S | ||
78 | |||||
79 | |||||
80 | 2130PL/S | 2130BC | |||
81 | |||||
82 | |||||
83 | 1330NCC | ||||
84 | |||||
85 | |||||
86 | 0530BC | OFF | |||
87 | |||||
88 | |||||
89 | 2130BC | OFF | OFF | ||
90 | |||||
91 | |||||
92 | OFF | 0530PL/S | |||
93 | |||||
94 | |||||
95 | |||||
96 | X0530 | X0530 | AV | ||
97 | |||||
98 | |||||
99 | X1330 | X1330 | |||
100 | |||||
101 | |||||
FN 09MAR-22MAR25 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K5:L6 | K5 | =FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(K11:K111,DATA!$AM$3:$AM$19)),"") |
M5 | M5 | =FILTER(DATA!$AL$3:$AL$17,NOT(COUNTIF(M11:M111,DATA!$AL$3:$AL$17)),"") |
Dynamic array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Expression | =AND(COUNTIF($AU$18:$AU$44,G19)=0,COUNTIF(G$19:G$110,G19)>1) | text | NO |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Cell Value | contains "EDO-U" | text | YES |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Cell Value | contains "OFF-U" | text | YES |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Expression | =OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*")) | text | YES |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Expression | =OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*")) | text | YES |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Cell Value | contains "OFF" | text | YES |
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112 | Cell Value | contains "EDO" | text | YES |
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113 | Cell Value | ending with "?" | text | NO |
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21 | Cell Value | contains "AV" | text | NO |
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3 | Expression | =G$3=TODAY() | text | NO |
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3 | Expression | =COLUMN()=CELL("COL") | text | NO |
G5:T5 | Cell | contains an error | text | NO |
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9 | Expression | =COUNTIF(DATA!$AV$4:$AV$16,G$3)=1 | text | NO |
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113 | Cell Value | contains "OK" | text | NO |
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113 | Cell Value | contains "DEC" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
K25:M25 | List | =DATA!$AF$4:$AF$56 |
K35:M35 | List | =DATA!$AF$4:$AF$56 |
K96:M96 | List | =DATA!$AF$4:$AF$56 |
K99:M99 | List | =DATA!$AF$4:$AF$56 |
K44:M44 | List | =DATA!$AF$4:$AF$56 |
K47:M47 | List | =DATA!$AF$4:$AF$56 |
K50:M50 | List | =DATA!$AF$4:$AF$56 |
K53:M53 | List | =DATA!$AF$4:$AF$56 |
K56:M56 | List | =DATA!$AF$4:$AF$56 |
K59:M59 | List | =DATA!$AF$4:$AF$56 |
K62:M62 | List | =DATA!$AF$4:$AF$56 |
K65:M65 | List | =DATA!$AF$4:$AF$56 |
K68:M68 | List | =DATA!$AF$4:$AF$56 |
K71:M71 | List | =DATA!$AF$4:$AF$56 |
K74:M74 | List | =DATA!$AF$4:$AF$56 |
K77:M77 | List | =DATA!$AF$4:$AF$56 |
K80:M80 | List | =DATA!$AF$4:$AF$56 |
K83:M83 | List | =DATA!$AF$4:$AF$56 |
K86:M86 | List | =DATA!$AF$4:$AF$56 |
K89:M89 | List | =DATA!$AF$4:$AF$56 |
K31:M31 | List | =DATA!$AF$4:$AF$56 |